Skip to content

Instantly share code, notes, and snippets.

@arkuznetsov

arkuznetsov/MS_SQL_all_db_compression.sql

Last active January 5, 2025 06:58
Show Gist options
  • Save arkuznetsov/3775de77ae6d05db297cd61d2e34aef9 to your computer and use it in GitHub Desktop.
Save arkuznetsov/3775de77ae6d05db297cd61d2e34aef9 to your computer and use it in GitHub Desktop.
Download ZIP
MS_SQL_all_db_compression.sql - компрессия (compression) всех таблиц, всех баз
Raw
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'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment