Instantly share code, notes, and snippets.

arkuznetsov/MS_SQL_all_db_compression.sql

Last activeJanuary 5, 2025 06:58

Learn more about clone URLs
Clone this repository at <script src="https://gist.github.com/arkuznetsov/3775de77ae6d05db297cd61d2e34aef9.js"></script>
Save arkuznetsov/3775de77ae6d05db297cd61d2e34aef9 to your computer and use it in GitHub Desktop.

Clone this repository at <script src="https://gist.github.com/arkuznetsov/3775de77ae6d05db297cd61d2e34aef9.js"></script>

Save arkuznetsov/3775de77ae6d05db297cd61d2e34aef9 to your computer and use it in GitHub Desktop.

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

	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'

Movatterモバイル変換