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
