creating an existing db schema baseline

What is the best method of creating schema creation scripts that can be
stored into a version control system. The process of using em to
generate a script is not an appealing option. I am still learning the
MS Sql sys tables and have not found a useful list of all the codes &
types to join the tables etc.

mike

--
Posted via http://dbforums.comwukie <member30544@.dbforums.com> wrote in message news:<3242331.1060980041@.dbforums.com>...
> What is the best method of creating schema creation scripts that can be
> stored into a version control system. The process of using em to
> generate a script is not an appealing option. I am still learning the
> MS Sql sys tables and have not found a useful list of all the codes &
> types to join the tables etc.
>
> mike

I don't like the fact that all source code versioning systems are
using proprietary files instead of proven relational databases
(SourceSafe is not exception from this). The reasons for this are
probably RDBMS licensing costs in the past.

Database schema can be exported also as XML file, which can be further
manipulated. If you and your team have serious schema versioning needs
I suggest you to evaluate Meta Data Services in SQL Server 2000 and
XML. One article about this has been published in the MSDN Magazine:
http://msdn.microsoft.com/msdnmag/i...es/default.aspx

Metadata Repository can be created not only through Enterprise Manager
but also programmatically using Meta Data API. Further information
with examples can be found in Meta Data Services SDK 3.0, which can be
downloaded for free.

Sinisa Catic|||found what I was looking for...

in EM > Tools > Generate SQL Scripts. THis will create the total schema
of the existing database.

mike

any known issues with this tool??

--
Posted via http://dbforums.com

Creating a variable to hold a table/view name

Hello all,
Is it possible to create a variable in a SQL script to hold a table/view
name?
Alot of my scripts are union queries where each part refers to the same
table/view, and sometimes those sources need to be changed. It would be
nice if I could change the value of the variable instead of doing a find &
replace to change the source's name.
Thanks for any help anyone can provide,
Conan Kelly
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:8LHDh.49165$5j1.25117@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is it possible to create a variable in a SQL script to hold a table/view
> name?
> Alot of my scripts are union queries where each part refers to the same
> table/view, and sometimes those sources need to be changed. It would be
> nice if I could change the value of the variable instead of doing a find &
> replace to change the source's name.
> Thanks for any help anyone can provide,
> Conan Kelly
>
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Creating a variable to hold a table/view name

Hello all,
Is it possible to create a variable in a SQL script to hold a table/view
name?
Alot of my scripts are union queries where each part refers to the same
table/view, and sometimes those sources need to be changed. It would be
nice if I could change the value of the variable instead of doing a find &
replace to change the source's name.
Thanks for any help anyone can provide,
Conan Kelly"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:8LHDh.49165$5j1.25117@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is it possible to create a variable in a SQL script to hold a table/view
> name?
> Alot of my scripts are union queries where each part refers to the same
> table/view, and sometimes those sources need to be changed. It would be
> nice if I could change the value of the variable instead of doing a find &
> replace to change the source's name.
> Thanks for any help anyone can provide,
> Conan Kelly
>
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Creating a variable to hold a table/view name

Hello all,
Is it possible to create a variable in a SQL script to hold a table/view
name?
Alot of my scripts are union queries where each part refers to the same
table/view, and sometimes those sources need to be changed. It would be
nice if I could change the value of the variable instead of doing a find &
replace to change the source's name.
Thanks for any help anyone can provide,
Conan Kelly"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:8LHDh.49165$5j1.25117@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is it possible to create a variable in a SQL script to hold a table/view
> name?
> Alot of my scripts are union queries where each part refers to the same
> table/view, and sometimes those sources need to be changed. It would be
> nice if I could change the value of the variable instead of doing a find &
> replace to change the source's name.
> Thanks for any help anyone can provide,
> Conan Kelly
>
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Creating a DB script

Somebody knows a tool able to generate DB scripts from a .mdf file?

Management Studio Express.

Right-click on the database and choose 'Script Database As..." and select an option. You can also script individual objects. This does not include data, you'd need to use BCP for data.

Management Studio Express CTP is at http://www.microsoft.com/downloads/details.aspx?familyid=57856cdd-da9b-4ad0-9a8a-f193ae8410ad&displaylang=en.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||I already tried Managment Studio Express, but for some reason it cannot access my .mdf file that is located at:
C:\Documents and Settings\Neto\Mis documentos\Visual Studio 2005\Projects\ProjectName\Database\myBD.mdf

The tree view that browses the folders won't let me get past 'My Documents'|||

SQL Express runs as the Network Services account, which is a limited access account that doesn't have permissions for a number of things, including User Profile directories.

If this is a one time operation, I would suggest detaching the database and moving it to a location where you can access it (such as the MSSQL\Data directory), attaching it there and then using Management Studio to generate the scripts.

If this is a task you need to do repeatedly, you'll probably need a programatic way to do this. I haven't been able to find a T-SQL method for doing this, maybe someone else knows of one. If not, you can do this from SMO, which you can get an overview of starting at http://msdn2.microsoft.com/en-us/library/ms162557.aspx.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1