T-SQL Script to Generate SHRINKFILE statements for all DBs

This a t-sql script I use to generate DBCC SHRINKFILE statements for all databases on a SQL Server 2005/2008. Microsoft recommends that you change the recovery model to simple shrink the file and change back to full if the recovery model is full to begin with. You cannot shrink readonly dbs (or offile dbs obviously)

The script is provided AS IS.

use master;

GO

create procedure [dbo].[udp_genShrinkStatements] as begin

declare @sql nvarchar(max);

declare @filename varchar(256);

declare @dbid int;

declare @dbname varchar(256);

declare @recoveryModel int; -- 3 simple; 1 full

declare dbcursor cursor for SELECT     database_id,[name], recovery_model --, recovery_model_desc

FROM         sys.databases

WHERE     (database_id > 4) AND (is_read_only = 0) and (state = 0) --skip system, readonly and offile dbs

ORDER BY   [name]

open dbcursor;

set @sql = '';

fetch next from dbcursor into @dbid,@dbname,@recoveryModel;

while @@FETCH_STATUS = 0

begin

declare filecursor cursor for SELECT [name] from sys.sysaltfiles where dbid = @dbid;

open filecursor;

select @dbname = '['+@dbname+']';

--select @sql = @sql + @dbname;

-- print '--'+@dbname;

--select @sql = @sql + '--';

-- print 'USE ' + @dbname +';';

select @sql = @sql + 'USE ' + @dbname +';' ;

-- print 'GO';

--select @sql = @sql + ' GO ';

if @recoveryModel = 1

begin

-- print 'ALTER DATABASE '+@dbname +' SET RECOVERY SIMPLE WITH NO_WAIT;';

select @sql = @sql + 'ALTER DATABASE '+@dbname +' SET RECOVERY SIMPLE WITH NO_WAIT;';

end

fetch next from filecursor into @filename

while @@FETCH_STATUS = 0

begin

-- print 'DBCC SHRINKFILE('''+ @filename +''');';

select @sql = @sql + 'DBCC SHRINKFILE('''+ @filename +''');';

fetch next from filecursor into @filename

end

close filecursor

deallocate filecursor

if @recoveryModel = 1

begin

-- print 'ALTER DATABASE '+@dbname +' SET RECOVERY FULL WITH NO_WAIT;';

select @sql = @sql + 'ALTER DATABASE '+@dbname +' SET RECOVERY FULL WITH NO_WAIT;';

end

-- print '---------------';

--select @sql = @sql + '---------------';

fetch next from dbcursor into @dbid,@dbname,@recoveryModel;

end

close dbcursor;

deallocate dbcursor;

--exec sp_executesql @stmt = @sql;

SELECT @sql;

end