Scripting a database template

The problem I am having is that I want to replace the sql script language in
a script and call individual files containing that language from our source
control.
How can I call/run multiple .sql files against a database from a single
script file?It depends on what tool you are using to execute the script. OSQL and SQLCMD
has the :r option. The
sub-script cannot have GO in it, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Mick" <nospam@.nospam.com> wrote in message news:12o3bl2hfjlod55@.corp.supernews.com..
.
> The problem I am having is that I want to replace the sql script language
in a script and call
> individual files containing that language from our source control.
> How can I call/run multiple .sql files against a database from a single sc
ript file?
>

Scripting a database template

The problem I am having is that I want to replace the sql script language in
a script and call individual files containing that language from our source
control.
How can I call/run multiple .sql files against a database from a single
script file?It depends on what tool you are using to execute the script. OSQL and SQLCMD has the :r option. The
sub-script cannot have GO in it, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Mick" <nospam@.nospam.com> wrote in message news:12o3bl2hfjlod55@.corp.supernews.com...
> The problem I am having is that I want to replace the sql script language in a script and call
> individual files containing that language from our source control.
> How can I call/run multiple .sql files against a database from a single script file?
>sql

Scriptifying in SQL 2005 Express

How does one scriptify all database objects using SQL Server 2005 Express / Server Management Studio Express?

I can see how to scriptify individual objects, but I'm not so keen on doing that for 200+ objects!

Thanks in advance

Dave

davebartlett.net

1. Right button on database

2. Select Tasks

3. select Generate Scripts

4. follow the instructions

Madhu

|||

Thanks, that saved a lot of time.

Do you know how to script all objects, but generate 1 script file per object?

The Wizard doesn't seem to allow that mode, it seems to put all generated scripts in a single file, which is difficult to navigate through when editing.

|||

for that you should apply SP2 ... sp2 have all the feature like in SQL 2000... in "OutPut Option window you have all these features... if it is not there in your machine means, yours is not SP2

Madhu

|||

Yes, I've installed SP2 and I can now scriptify each object to a separate file.

This is a great feature, thanks for your help.

Scriptifying in SQL 2005 Express

How does one scriptify all database objects using SQL Server 2005 Express / Server Management Studio Express?

I can see how to scriptify individual objects, but I'm not so keen on doing that for 200+ objects!

Thanks in advance

Dave

davebartlett.net

1. Right button on database

2. Select Tasks

3. select Generate Scripts

4. follow the instructions

Madhu

|||

Thanks, that saved a lot of time.

Do you know how to script all objects, but generate 1 script file per object?

The Wizard doesn't seem to allow that mode, it seems to put all generated scripts in a single file, which is difficult to navigate through when editing.

|||

for that you should apply SP2 ... sp2 have all the feature like in SQL 2000... in "OutPut Option window you have all these features... if it is not there in your machine means, yours is not SP2

Madhu

|||

Yes, I've installed SP2 and I can now scriptify each object to a separate file.

This is a great feature, thanks for your help.

Script SQL 2005 database to individual files?

I am trying to script the DROP(IF EXISTS) and CREATE for all of my
tables, views, stored procs, and functions to individual SQL text files
(one per object). This was trivially done in SQL 2000 with Enterprise
Manager, but when I try in SQL 2005 through Management Studio my only
"script mode" options are:

- Script to file (which is one huge file with everything)
- Script to Clipboard
- Script to New Query Window

FYI, I get to this screen through Management Studio by right clicking
on a database and selecting Tasks > Generate Scripts... > Next (doesn't
seem to matter what combo of objects I select to script or what other
options). I am using SQL Server 2005 Developer (which is Microsoft SQL
Server Management Studio 9.00.1399.00).

Any solution to this (i.e. via Management Studio, command line, etc.)
would be greatly appreciated.

Thanks.
TedMSDN/BOL indicates that this is possible here
http://msdn2.microsoft.com/en-us/library/ms191299.aspx. However, it
doesn't give any clue on how to accomplish it.

Quote from that page: "The schema for generated objects can be saved in
a single SQL Script file, or in several files with each file containing
the schema of just one object."|||Ted O'Connor (toconnor@.gmail.com) writes:
> I am trying to script the DROP(IF EXISTS) and CREATE for all of my
> tables, views, stored procs, and functions to individual SQL text files
> (one per object). This was trivially done in SQL 2000 with Enterprise
> Manager, but when I try in SQL 2005 through Management Studio my only
> "script mode" options are:
> - Script to file (which is one huge file with everything)
> - Script to Clipboard
> - Script to New Query Window
> FYI, I get to this screen through Management Studio by right clicking
> on a database and selecting Tasks > Generate Scripts... > Next (doesn't
> seem to matter what combo of objects I select to script or what other
> options). I am using SQL Server 2005 Developer (which is Microsoft SQL
> Server Management Studio 9.00.1399.00).
> Any solution to this (i.e. via Management Studio, command line, etc.)
> would be greatly appreciated.

Rather than hackiong SMO on your own, I don't think there is one.
Note that there are really two features from SQL 2000 you are missing:
1) One object per file.
2) A script that performs both DROP and CREATE.

There is this suggestion on MSDN Product Feedback Centre,
http://lab.msdn.microsoft.com/produ...px?feedbackid=9
eb6c773-2dbb-4a27-b9d8-225d6ed4385a
the by far most voted-on item for SQL Server.

I did actually only find one item that brings up one file per object,
http://lab.msdn.microsoft.com/produ...px?feedbackid=2
7695db7-cef6-42c3-9cb0-ac30583bbee9
there are no votes here, beside the submitted, but that is because the
bug has not been validated.

In any case, that would be better as a suggestion, as it is not a bug
that you can't script per object, just a poor design. So if you can't
find a suggestion on that theme - submit one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx