CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2ssms
SQL Police
SQL Police

Reputation: 4206

SQL Server 2008 Management Console: Automatically write results to a script and execute it

I would like to write a script which produces various statements out of a database.

Something like :

select 'DROP TABLE ['+ name + ']' from sys.objects where type = 'T' ;

I would like to automatically collect all output of such statements in a new file, and then I would like to execute this file. Is this possible ?

Important: The output should of course be without headers and without any other error/success, messages and so on.

In the optimal case, all necessary options for this should be set in the script itself, other than setting them in the user interface.

Upvotes: 1

Views: 1271

Answers (2)

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

First, you create you dynamic script, something like...

DECLARE @sql NVARCHAR(MAX)  
SELECT @sql = 
     COALESCE(@sql + CHAR(13), '') + 
     'DROP TABLE ['+ name + ']' 
FROM sys.objects 
WHERE [type] = 'T'

Execute that...

EXEC(@sql)

Then print that out to Messages-window...

PRINT @sql

And finally go to Messages-window, right-click it, select "Save results as...", Save as type > all files, and write your file name like myfile.sql

EDIT

I would never, EVER execute something like this automatically and without transaction. I'd rather save a script from Messages-window, open it, review it and then execute.

Upvotes: 4

Kane
Kane

Reputation: 16812

Are you wanting something like this?

If you only want to print the script use osql and a script something like this

DECLARE @schema VARCHAR(255)
DECLARE @table VARCHAR(255)

DECLARE PrintOutputCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT [TABLE_SCHEMA], [TABLE_NAME]
    FROM INFORMATION_SCHEMA.TABLES
OPEN PrintOutputCursor
FETCH NEXT FROM PrintOutputCursor INTO @schema, @table
WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT 'DROP TABLE ['+ @schema + '].[' + @table + '];'
    FETCH NEXT FROM PrintOutputCursor INTO @schema, @table
END
CLOSE PrintOutputCursor
DEALLOCATE PrintOutputCursor

If you want to execute the script using osql use this script (NOT RECOMMENDED)

DECLARE @schema VARCHAR(255)
DECLARE @table VARCHAR(255)
DECLARE @exec VARCHAR(4000)

DECLARE PrintOutputCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT [TABLE_SCHEMA], [TABLE_NAME]
    FROM INFORMATION_SCHEMA.TABLES
OPEN PrintOutputCursor
FETCH NEXT FROM PrintOutputCursor INTO @schema, @table
WHILE @@FETCH_STATUS = 0 BEGIN
    SET @exec = 'DROP TABLE ['+ @schema + '].[' + @table + '];'
    -- Uncomment the following to execute the dynamic statement
    -- EXEC (@exec)
    FETCH NEXT FROM PrintOutputCursor INTO @schema, @table
END
CLOSE PrintOutputCursor
DEALLOCATE PrintOutputCursor

Upvotes: 2

Related Questions