收缩当前SQL Server实例中所有的数据库

时　间:2018-01-05 09:23:06
作　者:缪炜   ID：24010  城市：江阴
摘　要:收缩当前SQL Server实例中所有的数据库
正　文:

DECLARE @sql nvarchar(300)
  
DECLARE ShrinkAllDBs CURSOR
READ_ONLY
FOR select name from sysdatabases
  
DECLARE @name nvarchar(255)
OPEN ShrinkAllDBs
  
FETCH NEXT FROM ShrinkAllDBs INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        SET @sql = N'DBCC SHRINKDATABASE(N''' + @name + N''')'
        EXEC sp_executesql @sql
    END
    FETCH NEXT FROM ShrinkAllDBs INTO @name
END
  
CLOSE ShrinkAllDBs
DEALLOCATE ShrinkAllDBs
GO