DECLARE @sql VARCHAR(max)
DECLARE @sqllog VARCHAR(max)
DECLARE @dbNAME VARCHAR(max)
DECLARE myCURSOR CURSOR FOR
SELECT 'use '+ NAME +' DUMP TRANSACTION '+NAME +' WITH NO_LOG',NAME FROM sys.databases
OPEN myCURSOR
FETCH NEXT FROM myCURSOR INTO @sql ,@dbNAME
WHILE(@@FETCH_status=0)
BEGIN
BEGIN TRY
EXEC(@sql)
SET @sqllog='use '+@dbNAME + ' DECLARE @NAME VARCHAR(50)
SELECT @NAME=NAME FROM sys.sysfiles where groupid=0
DBCC SHRINKFILE (@NAME, 0, TRUNCATEONLY) '
EXEC(@sqllog)
SET @sqllog='use '+@dbNAME + ' DECLARE @NAME VARCHAR(50)
SELECT @NAME=NAME FROM sys.sysfiles where groupid=1
DBCC SHRINKFILE (@NAME, 0, TRUNCATEONLY) '
EXEC(@sqllog)
END TRY
BEGIN CATCH
PRINT 'ERROR'
END CATCH;
FETCH NEXT FROM myCURSOR INTO @sql,@dbNAME
END
CLOSE myCURSOR
deallocate myCURSOR
数据库批量压缩sql代码
最新推荐文章于 2023-06-13 13:56:23 发布