Fransiscus Setiawan .NET Blog

CREATE TABLE #temp_dbs_table
(
[db_name] sysname NOT NULL PRIMARY KEY,
[mod] tinyint NOT NULL DEFAULT 1
)
 
INSERT INTO #temp_dbs_table ([db_name])
SELECT
name
FROM
master..sysdatabases
WHERE
dbid > 4 --- skip master, tempdb, model and msdb databases
 
declare @db_name sysname
 
SET @db_name = ''
 
while @db_name IS NOT NULL
begin
SET @db_name = NULL
 
SELECT top 1 @db_name = [db_name] FROM #temp_dbs_table where [mod] = 1
 
IF @db_name IS NULL
break
 
print '--------------------------------------------------'
 
print '> Database: ' + @db_name
 
print '> Changing recovery mode to simple'
 
declare @n_cmd nvarchar(4000)
 
SET @n_cmd = 'alter database [' + @db_name + '] set recovery simple'
 
exec sp_executesql @n_cmd
 
print '> Shrinking database'
 
SET @n_cmd = 'dbcc shrinkdatabase([' + @db_name + '])'
 
exec sp_executesql @n_cmd
 
UPDATE #temp_dbs_table set [mod] = 0 where [db_name] = @db_name
end
 
DROP TABLE #temp_dbs_table