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.