PROBLEM: I need to find out all the tables in a database that are not using data compression.
SOLUTION: Here is the T-SQL code to list all tables in a database that are not compressed.
SELECT DISTINCT D.name [SchemaName]
, C.name [TableName]
, A.data_compression_desc
FROM sys.objects C WITH (NOLOCK)
INNER JOIN sys.partitions A WITH (NOLOCK) ON C.[object_id] = A.[object_id]
INNER JOIN sys.schemas D WITH (NOLOCK) ON C.[schema_id] = D.[schema_id]
WHERE A.data_compression = 0
AND C.[type] = 'U'
AND SCHEMA_NAME(C.schema_id) <> 'SYS'
ORDER BY D.name, C.name
GO
The script below will take care of generating the T-SQL commands necessary to compress all tables that are not already compressed on a database. This script will handle heap tables, clustered indexes and non-clustered indexes. Make sure to output the result to text (CTRL+T) so you can copy, paste and execute in a new window.
DECLARE @TSQL nvarchar(1000)
, @schema varchar(128)
, @tablename varchar(128)
, @type tinyint
, @indexname varchar(128)
DECLARE C CURSOR
FOR SELECT DISTINCT D.name [SchemaName]
, C.name [TableName]
, I.[name] [IndexName]
, I.[type]
FROM sys.objects C WITH (NOLOCK)
INNER JOIN sys.partitions A WITH (NOLOCK) ON C.[object_id] = A.[object_id]
INNER JOIN sys.schemas D WITH (NOLOCK) ON C.[schema_id] = D.[schema_id]
INNER JOIN sys.database_principals E WITH (NOLOCK) ON D.principal_id = E.principal_id
INNER JOIN sys.indexes I WITH (NOLOCK) ON A.index_id = I.index_id
AND A.object_id = I.object_id
WHERE A.data_compression = 0
AND C.[type] = 'U'
AND C.[name] NOT LIKE 'MS%'
AND I.object_id > 1000
AND SCHEMA_NAME(C.schema_id) <> 'SYS'
ORDER BY D.name, C.name
OPEN C
FETCH NEXT
FROM C
INTO @schema, @tablename, @indexname, @type
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @TSQL = CASE @type
WHEN 1 THEN N'ALTER TABLE ['+ @schema +'].['+ @tablename +'] REBUILD WITH (DATA_COMPRESSION = PAGE)' + CHAR(10) +'GO'+ CHAR(10)
WHEN 0 THEN N'ALTER TABLE ['+ @schema +'].['+ @tablename +'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)' + CHAR(10) +'GO'+ CHAR(10)
ELSE N'ALTER INDEX ['+ @indexname +'] ON ['+ @schema +'].['+ @tablename +'] REBUILD WITH (DATA_COMPRESSION = PAGE)' + CHAR(10) +'GO'+ CHAR(10) END
SELECT @TSQL
FETCH NEXT FROM C INTO @schema, @tablename, @indexname, @type
END
CLOSE C
DEALLOCATE C
GO
