Ever want to do a bulk analysis of the possible numbers of using page or row compression on a SQL Server 2008(+) Enterprise database? Now you can – easily.
This script came from http://sqlfool.com/2011/06/estimate-compression-savings/, so thanks for the great script!
SET NOCOUNT ON;
DECLARE @printOnly BIT = 0 — change to 1 if you don’t want to execute, just print commands IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE ‘%#tables%’) CREATE TABLE #tables IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE ‘%#compression%’) IF NOT EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE ‘%#compression%’) CREATE TABLE #compressionResults END; INSERT INTO #tables SELECT @tableCount = COUNT(*) FROM #tables; WHILE EXISTS(SELECT * FROM #tables WHERE processed = 0) SELECT TOP 1 @tableName = tableName SELECT @statusMsg = ‘Working on ‘ + CAST(((@tableCount – COUNT(*)) + 1) AS VARCHAR(10)) RAISERROR(@statusMsg, 0, 42) WITH NOWAIT; SET @sqlStatement = ‘EXECUTE sp_estimate_data_compression_savings ”’ IF @printOnly = 1 SELECT @sqlStatement; END INSERT INTO #compressionResults END; UPDATE #tables END; SELECT * |