Return to Snippet

Revision: 41010
at February 10, 2011 20:53 by scholli


Initial Code
USE [master]
GO

DECLARE @dbname SYSNAME
DECLARE @altercmd NVARCHAR(1000)
DECLARE @shrinkcmd NVARCHAR(1000)

DECLARE [dbcursor] CURSOR FOR SELECT [name] FROM sysdatabases

OPEN [dbcursor]
FETCH NEXT FROM [dbcursor] INTO @dbname

WHILE
    @@FETCH_STATUS = 0
BEGIN
    IF
        (SELECT DATABASEPROPERTYEX(@dbname, 'RECOVERY')) != 'SIMPLE'
        AND
        @dbname != 'tempdb'
    BEGIN
        SET @altercmd = 'ALTER DATABASE "' + @dbname
                                               + '" SET RECOVERY SIMPLE'
        EXEC (@altercmd)

        SET @shrinkcmd = 'DBCC SHRINKDATABASE ("' + @dbname + '")'
        EXEC (@shrinkcmd)

        PRINT @dbname
    END

    FETCH NEXT FROM [dbcursor] INTO @dbname
END

CLOSE [dbcursor]
DEALLOCATE [dbcursor]

Initial URL

                                

Initial Description

                                

Initial Title
Shrink all development databases

Initial Tags
sql, server

Initial Language
SQL