How to spool into a script file in SQL Server? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to spool into a script file in SQL Server?

Author

Topic

Ach
Starting Member

13 Posts

Posted - 2005-09-18 : 07:34:39

Hi friends,
Suppose that I want to generate an script that has a
"DROP TABLE <table_name>;" for each table in one of our databases.In Oracle I can do this using "spool" command in "SQL Plus" which is a commandline utility somewhat like isql or osql(I don't know these two perfectly! ).For example:

spool c:\DropAllTables.sql
       select "DROP TABLE "||table_name||";" from user_tables;
       spool off

And this will generate that script with many drop within that.Would you please clarify me, if there is such a thing in SQL Server 2000?
-Thanks a lot

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-09-18 : 09:30:59

Here's the SQL statement that will generate the DROP TABLE statements:

SELECT 'DROP TABLE ' + quotename(table_schema) + '.' + quotename(table_name) FROM myDatabase.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

To spool this to a file, you have two choices I can think of:

1) Run that command in Query Analyzer, and use the "Results to File" option under the Query menu;
2) Run that command using osql and direct the output to a file, like this:

osql -SmyServer -E -h-1 -n -Q"set nocount on; SELECT 'DROP TABLE ' + quotename(table_schema) + '.' + quotename(table_name) FROM myDatabase.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'" -o c:\DropAllTables.sql

That entire command should be on a single line.

OK, I lied, I can think of a third way:

3) Run that command using bcp to output the results:

bcp "SELECT 'DROP TABLE ' + quotename(table_schema) + '.' + quotename(table_name) FROM myDatabase.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'" queryout c:\DropAllTables.sql -SmyServer -T -c

bcp is a data import and export utility, since you are getting data from a table or query you can use it here to create the script file.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-18 : 10:26:23

You could also use Enterprise Manager's "Generate SQL Script..." to generate Drop scripts to a file.

Be One with the Optimizer
TG

Ach
Starting Member

13 Posts

Posted - 2005-09-19 : 03:27:58

Thanks guys,That was so useful for me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources