How to spool into a script file in SQL Server?

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:
[code]spool c:\DropAllTables.sql
select "DROP TABLE "||table_name||";" from user_tables;
spool off
[/code]
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

Answer this question

How to spool into a script file in SQL Server?

mkruluts

The previous solution isn't general enough.

I have vendor delivered scripts that I have to run in query analyzer. Typically these scripts do a "before" select against a table, then some update/insert/delete statement(s), then an "after" select to show the updated rows. I need an output file that shows the SQL statements and their results for auditing purposes.

As mentioned, in Oracle I do "spool c:\temp\spoolfile.txt" then run the statements. How do I do this using Query Analyzer
Siggy01
That SQLCMD is quite useful.

Is there a way to control what appears in the file For example, I ran

select getdate()

and got the following results.

------------------------------------

2007-11-06 15:06:31.570

(1 rows affected)

but I desire only:

2007-11-06 15:06:31.570

in other words, without the ------ and the (1 rows affected).
Pleb
Have you been able to resolve this issue We here initially Oracle shop now having SQL Server have the same need. We have research the web and SQL documentation with no luck
rexwrx
Hi
I think you may use the sp_msforeachtable stored procedure
try this snippet

sp_msforeachtable 'PRINT ''DROP '''

then you can make the output of the query analyzer into a file the use this file
Robert Flaming

I'm getting closer.

I load the script in Query Analyzer. I select Tools -> Options -> Results tab and set:

- Results to Text

- Check "output query"

- Check "Print Column Headers"

I can then highlight the first SQL statement, run it, cut out the results and paste it into a Notepad doc. This process is repeated for each of the remaining SQL statements. It's slow and tedious - someone please tell me there's a better method.
ShimpiSuhas

set nocount on
sharathkumarmv

We have many options. Here are just a few that I think can help solve your problem...

SQL Server 2000:
OSQL/ISQL - SQL Server 2000 command line tools.
SQL DMO - Management API
T-SQL

SQL Server 2005:
SQLCMD - Command line tool
SQL SMO - Management API (really easy to script in .NET)
Generate Script Wizard (You choose the objects in your database that you want to script and the settings. It generates the scripts for you.) (In SQL Server 2005 SP2, you can generate DROP statements for objects and script each object to its own file.)
T-SQL

Here is a T-SQL Code Snippet:

SELECT 'DROP TABLE [' + s.name + '].[' + t.name + '];'
FROM sys.tables t
JOIN sys.schemas s on s.schema_id = t.schema_id

If you want to save the results from this T-SQL script to a file, you can:

1) Have Management Studio save it to a file:
Query | Results To | Results to File or Shortcut Key (CTRL + SHIFT + F)

2) Use SQLCMD to output the results to a file:
a) Save the T-SQL query above to a .sql file
b) Use the -e option to echo your input file into your output file
SQLCMD -i Input.sql -o C:\Results.txt -e

Please let me know if this answers your question.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
How to spool into a script file in SQL Server?

Answer this question