Stored Proc to shrink all Databases on Sql Server Instance

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

Posted in: SQL,SQL_UTILITY_SPs