List Uncompressed Tables and Indexes

DROP PROCEDURE dbo.UncompressedObjects

GO

CREATE PROCEDURE dbo.UncompressedObjects

(

@database VARCHAR(50) = '' ,

@emailrecipients VARCHAR(1000) = '' ,

@emailprofile VARCHAR(50) = '' ,

@compressiontype VARCHAR(4) = 'PAGE'

)

AS

BEGIN

/*

Procedure : dbo.UncompressedObjects

Version : 1.0 (August 2009)

Author : Richard Doering

Web : http://sqlsolace.blogspot.com

Usage :

UncompressedObjects has 2 required parameters, @database and @compressiontype.

@database is the name of the database you want to compress.

@compressiontype is either PAGE or ROW compression.

There are 2 optional parameters @emailrecipients and @emailprofile.

If these are both supplied, the output is sent to the email addresses supplied (providing you have an email profile configured).

Output :

The procedure provides >

Lists of tables & indexes without compression

Lists of tables & indexes not using the desired compression (e.g. ROW when you've specified a compression type of PAGE)

TSQL commands to compress the database objects.

Examples :

EXEC dbo.UncompressedObjects

@database = 'master'

, @compressiontype = 'ROW'

EXEC dbo.UncompressedObjects

@database = 'Adventureworks'

, @compressiontype = 'PAGE'

, @emailrecipients = 'emailaddress@domain.com'

, @emailprofile = 'Profile Name'

*/

SET NOCOUNT ON

-- Check supplied parameters

IF @database = ''

BEGIN

PRINT 'Database not specified'

RETURN

END

IF @database NOT IN (SELECT name FROM sys.databases)

BEGIN

PRINT 'Database ' + @database + ' not found on server ' + @@SERVERNAME

RETURN

END

IF @emailrecipients = '' AND @emailprofile <> ''

BEGIN

PRINT 'Email profile given but recipients not specified'

RETURN

END

IF @emailrecipients <> '' AND @emailprofile = ''

BEGIN

PRINT 'Email recipients given but profile not specified'

RETURN

END

SET @compressiontype = UPPER(LTRIM(RTRIM(@compressiontype)))

IF @compressiontype NOT IN ('PAGE', 'ROW')

BEGIN

PRINT 'CompressionType must be PAGE or ROW'

RETURN

END

-- Declare variables

DECLARE

@indexreport VARCHAR(MAX),

@missingindexcompressiontsql VARCHAR(MAX),

@missingindextablelist VARCHAR(MAX),

@missingindexindexlist VARCHAR(MAX),

@missingcompressiontablecount INT,

@missingcompressionindexcount INT,

@changeindexcompressiontsql VARCHAR(MAX),

@changeindextablelist VARCHAR(MAX),

@changeindexindexlist VARCHAR(MAX),

@changecompressiontablecount INT,

@changecompressionindexcount INT,

@CurrentRow INT,

@TotalRows INT,

@Objecttype VARCHAR(10),

@objectname VARCHAR(100),

@command VARCHAR(1000),

@emailsubject VARCHAR(100),

@dynamicsql VARCHAR(MAX)

-- Create temporary tables.

-- These are used because they're scope is greater than a tablevariable i.e. we can pull results back from dynamic sql.

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '##MissingCompression%')

DROP TABLE ##MissingCompression

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '##ChangeCompression%')

DROP TABLE ##ChangeCompression

CREATE TABLE ##MissingCompression

(

uniquerowid INT IDENTITY ( 1 , 1 ) PRIMARY KEY NOT NULL,

objecttype VARCHAR(10),

objectname VARCHAR(100),

command VARCHAR(500)

);

CREATE TABLE ##ChangeCompression

(

uniquerowid INT IDENTITY ( 1 , 1 ) PRIMARY KEY NOT NULL,

objecttype VARCHAR(10),

objectname VARCHAR(100),

command VARCHAR(500)

);

-- Work out what indexes are missing compression and build the commands for them

SET @dynamicsql =

'WITH missingcompression AS (SELECT ''Table'' AS objecttype,

s.name + ''.'' + o.name AS objectname,

''ALTER TABLE ['' + s.name + ''].['' + o.name + ''] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION == ' + @compressiontype + ');'' AS command

FROM ' + @database + '.sys.objects o

INNER JOIN ' + @database + '.sys.partitions p

ON p.object_id = o.object_id

INNER JOIN ' + @database + '.sys.schemas s

ON s.schema_id = o.schema_id

WHERE TYPE = ''u''

AND data_compression = 0

AND Schema_name(o.schema_id) <> ''SYS''

UNION

SELECT ''Index'' AS objecttype,

i.name AS objectname,

''ALTER INDEX ['' + i.name + ''] ON ['' + s.name + ''].['' + o.name + ''] REBUILD WITH ( DATA_COMPRESSION = ' + @compressiontype + ');'' AS command

FROM ' + @database + '.sys.dm_db_partition_stats ps

INNER JOIN ' + @database + '.sys.indexes i

ON ps.[object_id] = i.[object_id]

AND ps.index_id = i.index_id

AND i.type_desc <> ''HEAP''

INNER JOIN ' + @database + '.sys.objects o

ON o.[object_id] = ps.[object_id]

INNER JOIN ' + @database + '.sys.schemas s

ON o.[schema_id] = s.[schema_id]

AND s.name <> ''SYS''

INNER JOIN ' + @database + '.sys.partitions p

ON p.[object_id] = o.[object_id]

AND data_compression = 0)

-- populate temporary table ''##MissingCompression''

INSERT INTO ##MissingCompression (objecttype, objectname, command)

SELECT objecttype, objectname, command FROM missingcompression ORDER BY objectname ASC, command DESC '

EXEC (@dynamicsql)

SET @dynamicsql =

'WITH changecompression

AS (SELECT ''Table'' AS objecttype,

s.name + ''.'' + o.name AS objectname,

''ALTER TABLE ['' + s.name + ''].['' + o.name + ''] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ' + @compressiontype + ');'' AS command

FROM ' + @database + '.sys.objects o

INNER JOIN ' + @database + '.sys.partitions p

ON p.object_id = o.object_id

INNER JOIN ' + @database + '.sys.schemas s

ON s.schema_id = o.schema_id

WHERE TYPE = ''u''

AND data_compression <> 0

AND data_compression_desc <> ''' + @compressiontype + '''

AND Schema_name(o.schema_id) <> ''SYS''

UNION

SELECT ''Index'' AS objecttype,

i.name AS objectname,

''ALTER INDEX ['' + i.name + ''] ON ['' + s.name + ''].['' + o.name + ''] REBUILD WITH ( DATA_COMPRESSION = ' + @compressiontype + ');'' AS command

FROM ' + @database + '.sys.dm_db_partition_stats ps

INNER JOIN ' + @database + '.sys.indexes i

ON ps.[object_id] = i.[object_id]

AND ps.index_id = i.index_id

AND i.type_desc <> ''HEAP''

INNER JOIN ' + @database + '.sys.objects o

ON o.[object_id] = ps.[object_id]

INNER JOIN ' + @database + '.sys.schemas s

ON o.[schema_id] = s.[schema_id]

AND s.name <> ''SYS''

INNER JOIN ' + @database + '.sys.partitions p

ON p.[object_id] = o.[object_id]

AND data_compression <> 0

AND data_compression_desc <> ''' + @compressiontype + ''' )

-- populate temporary table ''##ChangeCompression''

INSERT INTO ##ChangeCompression (objecttype, objectname, command)

SELECT objecttype, objectname, command FROM changecompression ORDER BY objectname ASC, command DESC '

EXEC (@dynamicsql)

-- We now have populated our temporary tables (##MissingCompression & ##ChangeCompression)

-- First, loop objects with no compression.

-- For each object >

-- 1) increment the counter,

-- 2) add the object name to the list for display

-- 3) generate the tsql for compression commands

-- set initial variables

SET @missingindexcompressiontsql = ''

SET @missingindextablelist = ''

SET @missingindexindexlist = ''

SET @missingcompressiontablecount = 0

SET @missingcompressionindexcount = 0

SELECT @TotalRows = Count(* ) FROM ##MissingCompression

SELECT @CurrentRow = 1

WHILE @CurrentRow <= @TotalRows

BEGIN

SELECT @Objecttype = objecttype,

@objectname = objectname,

@command = command

FROM ##MissingCompression

WHERE uniquerowid = @CurrentRow

SET @missingindexcompressiontsql = @missingindexcompressiontsql + @command + Char(10) + Char(10)

IF @Objecttype = 'table'

BEGIN

SET @missingindextablelist = @missingindextablelist + @objectname + Char(10)

SET @missingcompressiontablecount = @missingcompressiontablecount + 1

END

IF @Objecttype = 'index'

BEGIN

SET @missingindexindexlist = @missingindexindexlist + @objectname + Char(10)

SET @missingcompressionindexcount = @missingcompressionindexcount + 1

END

SELECT @CurrentRow = @CurrentRow + 1

END

-- Now deal with Objects that need to change compression type

-- For each object >

-- 1) increment the counter,

-- 2) add the object name to the list for display

-- 3) generate the tsql for compression commands

-- set initial variables

SET @changeindexcompressiontsql = ''

SET @changeindextablelist = ''

SET @changeindexindexlist = ''

SET @indexreport = ''

SET @changecompressiontablecount = 0

SET @changecompressionindexcount = 0

SELECT @TotalRows = Count(* ) FROM ##ChangeCompression

SELECT @CurrentRow = 1

WHILE @CurrentRow <= @TotalRows

BEGIN

SELECT @Objecttype = objecttype,

@objectname = objectname,

@command = command

FROM ##ChangeCompression

WHERE uniquerowid = @CurrentRow

SET @changeindexcompressiontsql = @changeindexcompressiontsql + @command + Char(10) + Char(10)

IF @Objecttype = 'table'

BEGIN

SET @changeindextablelist = @changeindextablelist + @objectname + Char(10)

SET @changecompressiontablecount = @changecompressiontablecount + 1

END

IF @Objecttype = 'index'

BEGIN

SET @changeindexindexlist = @changeindexindexlist + @objectname + Char(10)

SET @changecompressionindexcount = @changecompressionindexcount + 1

END

SELECT @CurrentRow = @CurrentRow + 1

END

-- Build the text output for the report >

-- First for objects missing compression >

IF (@missingcompressionindexcount + @missingcompressiontablecount) > 0

BEGIN

IF (@missingcompressiontablecount) > 0

BEGIN

SET @indexreport = @indexreport + 'Tables not currently utilising ' + @compressiontype + ' compression >' + Char(10) + '--------------------------------------------' + Char(10) + @missingindextablelist + Char(13) + Char(13)

END

IF (@missingcompressionindexcount) > 0

BEGIN

SET @indexreport = @indexreport + 'Indexes not currently utilising ' + @compressiontype + ' compression >' + Char(10) + '---------------------------------------------' + Char(10) + @missingindexindexlist + Char(13) + Char(13)

END

END

-- Now for objects using the incorrect compression type >

IF (@changecompressionindexcount + @changecompressiontablecount) > 0

BEGIN

IF (@changecompressiontablecount) > 0

BEGIN

SET @indexreport = @indexreport + 'Tables with incorrect compression type >' + Char(10) + '--------------------------------------------' + Char(13) + Char(10) + @changeindextablelist + Char(13) + Char(10)

END

IF (@changecompressionindexcount) > 0

BEGIN

SET @indexreport = @indexreport + 'Indexes with incorrect compression type >' + Char(10) + '---------------------------------------------' + Char(13) + Char(10) + @changeindexindexlist + Char(13) + Char(10)

END

END

IF (@missingcompressionindexcount + @missingcompressiontablecount) > 0

BEGIN

SET @indexreport = @indexreport + char(10) + '/* TSQL to implement ' + @compressiontype + ' compression */' + Char(10) + '-----------------------------------' + Char(10) + 'USE [' + @database + ']' + Char(10) + 'GO' + Char(10) + @missingindexcompressiontsql + Char(13) + Char(10)

END

IF (@changecompressionindexcount + @changecompressiontablecount) > 0

BEGIN

SET @indexreport = @indexreport + char(10) + '/* TSQL to change to ' + @compressiontype + ' compression type */' + Char(10) + '-------------------------------------' + Char(10) + 'USE [' + @database + ']' + Char(10) + 'GO' + Char(10) + @changeindexcompressiontsql + Char(13) + Char(10)

END

-- Tidy up. Remove the temporary tables.

DROP TABLE ##MissingCompression

DROP TABLE ##ChangeCompression

-- Display report and email results if there are any required actions >

IF ( (@changecompressionindexcount + @changecompressiontablecount + @missingcompressionindexcount + @missingcompressiontablecount) > 0)

BEGIN

-- Compression changes recommended, display them

PRINT @indexreport

-- If email paramters supplied, email the results too.

IF @emailrecipients <> '' AND @emailprofile <> ''

BEGIN

SET @emailsubject = @@SERVERNAME + ' : Uncompressed object report : ' + @database + ' (' + @compressiontype + ' compression)'

-- send email

EXEC msdb.dbo.sp_send_dbmail

@recipients = @emailrecipients,

@subject = @emailsubject,

@body = @indexreport,

@profile_name = @emailprofile

END

END

ELSE

BEGIN

PRINT 'No database objects to compress'

END

END

GO

DROP PROCEDURE dbo.UncompressedServerObjects

GO

CREATE PROCEDURE dbo.UncompressedServerObjects AS

BEGIN

SET NOCOUNT ON

DECLARE @CurrentRow INT

DECLARE @TotalRows INT

DECLARE @DatabaseName NVARCHAR(255)

DECLARE @Databases TABLE(

UNIQUEROWID INT IDENTITY ( 1,1 ) PRIMARY KEY NOT NULL,

DATABASENAME NVARCHAR(255)

)

SELECT @CurrentRow = 1

INSERT INTO @Databases (DATABASENAME)

SELECT NAME

FROM SYS.DATABASES

WHERE DATABASE_ID > 4

SELECT @TotalRows = COUNT(*) FROM @Databases

WHILE @CurrentRow <= @TotalRows

BEGIN

SELECT @DatabaseName = DATABASENAME

FROM @Databases

WHERE UNIQUEROWID = @CurrentRow

EXEC dbo.UncompressedObjects

@database = @DatabaseName

, @compressiontype = 'PAGE'

, @emailrecipients = 'emailaddress@domain.com'

, @emailprofile = 'Profile Name'

SELECT @CurrentRow = @CurrentRow + 1

END

END

GO