Executing T-SQL against all databases on a server.

I have occasionally found it convenient to execute a command against all the databases on a server, such as ensuring that all the databases on my test-server have RECOVERY MODE set to SIMPLE, and performing a SHRINK operation to reclaim space on the disk for each of them. Googling this problem there were quite a lot of samples like this, but here is what worked for me:

DECLARE @dbname char(50) 
DECLARE c1 CURSOR READ_ONLY FOR 
    SELECT name 
    FROM sys.databases
    WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')

OPEN c1 
FETCH NEXT FROM c1 INTO @dbname 

WHILE @@FETCH_STATUS = 0 
BEGIN 
    PRINT 'Opdaterer ' + @dbname
    EXEC('ALTER DATABASE ' + @dbname + 'SET RECOVERY SIMPLE;');
    EXEC('DBCC SHRINKDATABASE(N'''+ @dbname + ''');');
    FETCH NEXT FROM c1 INTO @dbname;
END 
CLOSE c1 DEALLOCATE c1 

 

This is of course very easy to modify for other purposes. Some posts warned against bad performance on cursors, but this isn’t really an issue in this scenario.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s