Determine compression on SQL Server database tables

DECLARE @printOnly BIT = 0 — change to 1 if you don’t want to execute, just print commands
, @tableName VARCHAR(256)
, @schemaName VARCHAR(100)
, @sqlStatement NVARCHAR(1000)
, @tableCount INT
, @statusMsg VARCHAR(1000);

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE ‘%#tables%’)
DROP TABLE #tables;

CREATE TABLE #tables
(
database_name sysname
, schemaName sysname NULL
, tableName sysname NULL
, processed BIT
);

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE ‘%#compression%’)
DROP TABLE #compressionResults;

IF NOT EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE ‘%#compression%’)
BEGIN

CREATE TABLE #compressionResults
(
objectName VARCHAR(100)
, schemaName VARCHAR(50)
, index_id INT
, partition_number INT
, size_current_compression BIGINT
, size_requested_compression BIGINT
, sample_current_compression BIGINT
, sample_requested_compression BIGINT
);

END;

INSERT INTO #tables
SELECT DB_NAME()
, SCHEMA_NAME([schema_id])
, name
, 0 — unprocessed
FROM sys.tables;

SELECT @tableCount = COUNT(*) FROM #tables;

WHILE EXISTS(SELECT * FROM #tables WHERE processed = 0)
BEGIN

SELECT TOP 1 @tableName = tableName
, @schemaName = schemaName
FROM #tables WHERE processed = 0;

SELECT @statusMsg = ‘Working on ‘ + CAST(((@tableCount – COUNT(*)) + 1) AS VARCHAR(10))
+ ‘ of ‘ + CAST(@tableCount AS VARCHAR(10))
FROM #tables
WHERE processed = 0;

RAISERROR(@statusMsg, 0, 42) WITH NOWAIT;

SET @sqlStatement = ‘EXECUTE sp_estimate_data_compression_savings ”’
+ @schemaName + ”’, ”’ + @tableName + ”’, NULL, NULL, ”PAGE”;’ — ROW, PAGE, or NONE

IF @printOnly = 1
BEGIN

SELECT @sqlStatement;

END
ELSE
BEGIN

INSERT INTO #compressionResults
EXECUTE SP_EXECUTESQL @sqlStatement;

END;

UPDATE #tables
SET processed = 1
WHERE tableName = @tableName
AND schemaName = @schemaName;

END;

SELECT *
FROM #compressionResults;