MS_SQL_all_db_compression.sql - компрессия (compression) всех таблиц, всех баз

MS_SQL_all_db_compression.sql

EXEC sp_MSforeachdb 'USE [?]
IF NOT (''?'' = ''master'' OR ''?'' = ''model'' OR ''?'' = ''msdb'' OR ''?'' = ''tempdb'')
BEGIN
	SELECT DISTINCT
		[tables].name AS TName
	INTO #tmpTables
	FROM sys.tables [tables]
	LEFT JOIN sys.indexes [indexes]
	ON [tables].object_id = [indexes].object_id
	LEFT JOIN sys.partitions p
	ON [indexes].index_id = p.index_id
		AND [tables].object_id = p.object_id
	WHERE p.data_compression_desc = ''NONE''

	DECLARE @TName nvarchar(2000)
	DECLARE @DBModifyed int
	SET @DBModifyed = 0

	SELECT TOP 1 @TName = TName FROM #tmpTables

	WHILE @@ROWCOUNT != 0
	BEGIN
		EXEC (''ALTER TABLE '' + @TName + '' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'')
		EXEC (''ALTER INDEX ALL ON '' + @TName + '' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'')
		DELETE #tmpTables WHERE TName = @TName
		SET @DBModifyed = 1
		SELECT TOP 1 @TName = TName FROM #tmpTables
	END
	IF @DBModifyed = 1
	BEGIN
		DBCC SHRINKDATABASE(N''?'', 0)
	END
	DROP TABLE #tmpTables
	SELECT ''?''
END'