Shrink All Databases In SQL 2008 Using A Query

IF EXISTS(select * from sysobjects where id = object_id('dbo.SP_ShrinkAllDatabasesOnServer') and xtype = 'P')

DROP PROCEDURE dbo.SP_ShrinkAllDatabasesOnServer

GO

CREATE PROCEDURE dbo.SP_ShrinkAllDatabasesOnServer

AS

BEGIN

CREATE TABLE #TempDatabasesTable

(

[DatabaseName] sysname not null primary key,

Mod tinyint not null default 1

)

INSERT INTO #TempDatabasesTable ([DatabaseName])

SELECT

name

FROM

master..sysdatabases

WHERE

dbid > 4

DECLARE @DatabaseName sysname

SET @DatabaseName = ''

WHILE @DatabaseName is not null

BEGIN

SET @DatabaseName = NULL

SELECT TOP 1 @DatabaseName = [DatabaseName] from #TempDatabasesTable where Mod = 1

IF @DatabaseName is NULL

break

print '*******************************************************************'

print '> DB: ' + @DatabaseName

print '> SET RECOVERY MODE SIMPLE'

declare @SqlCommand nvarchar(4000)

set @SqlCommand = 'ALTER DATABASE [' + @DatabaseName + '] SET recovery simple'

exec sp_executesql @SqlCommand

print '> Shrinking database'

set @SqlCommand = 'dbcc shrinkdatabase([' + @DatabaseName + '])'

exec sp_executesql @SqlCommand

update #TempDatabasesTable set Mod = 0 where [DatabaseName] = @DatabaseName

end

DROP TABLE #TempDatabasesTable

END

GO

exec dbo.SP_ShrinkAllDatabasesOnServer