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
03 May 2023
SQL - Shrink all user databases
Subscribe to:
Posts (Atom)