03 May 2023

SQL - Shrink all user databases

USE MASTER
GO

CREATE OR ALTER PROCEDURE Boso_ShrinkAllUserDB_sp
(
    @DBName AS sysname = NULL
)
AS
BEGIN
    --##2023-15-24 - Boso -             @DBName
    SET NOCOUNT ON

    DECLARE @SQ NVARCHAR(MAX)

    DECLARE #CUR CURSOR FOR
    SELECT  CONCAT('; PRINT ''** ', NAME ,' '' ; DBCC SHRINKDATABASE(', QUOTENAME(NAME) ,')') AS COMANDO
    FROM    SYS.DATABASES
    WHERE   STATE = 0           --> ONLINE DB ONLY
        AND DATABASE_ID > 4     --> SKIP SYSTEM DBS
        AND (@DBName IS NULL OR name = @DBName)


    OPEN #CUR
    FETCH NEXT FROM #CUR INTO @SQ

    WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC SP_EXECUTESQL @SQ

            FETCH NEXT FROM #CUR INTO @SQ
        END


    CLOSE #CUR
    DEALLOCATE #CUR

END
GO