Windows Group Membership Checker

SQLServerCentral.com have published a script I use to look at Windows Group Membership. This is good if you use AD groups to manage security...

SSC : Windows Group Membership Checker



March 2011 Update : Putting the function here too now as SSC exclusivity period over...

/*
Script  : SQL Server - Windows Group Membership Checker
Version : 1.0 (August 2010)
Author  : Richard Doering
Web     : http://sqlsolace.blogspot.com
*/

DECLARE @CurrentRow INT
DECLARE @TotalRows INT
SET @CurrentRow = 1

DECLARE  @SqlGroupMembership  TABLE(
    ACCOUNT_NAME      SYSNAME,
    ACCOUNT_TYPE      VARCHAR(30),
    ACCOUNT_PRIVILEGE VARCHAR(30),
    MAPPED_LOGIN_NAME SYSNAME,
    PERMISSION_PATH   SYSNAME
    )

DECLARE @WindowsGroupsOnServer TABLE(
   UniqueRowID int IDENTITY (1, 1) Primary key NOT NULL 
 , Name  SYSNAME
 )
 
INSERT INTO @WindowsGroupsOnServer (NAME)
SELECT [NAME] FROM master.sys.server_principals WHERE TYPE = 'G' 

SELECT @TotalRows = MAX(UniqueRowID) FROM @WindowsGroupsOnServer

DECLARE @WindowsGroupName sysname 


-- Loop Each Windows Group present on the server
WHILE @CurrentRow <= @TotalRows 
   BEGIN 
  
  SELECT @WindowsGroupName  = [Name] 
  FROM @WindowsGroupsOnServer
  WHERE UniqueRowID = @CurrentRow 
  
    BEGIN TRY
    -- Insert found logins into table variable
    INSERT INTO @SqlGroupMembership (ACCOUNT_NAME,ACCOUNT_TYPE,ACCOUNT_PRIVILEGE,MAPPED_LOGIN_NAME,PERMISSION_PATH)
       EXEC xp_logininfo @WindowsGroupName , 'members' 
    END TRY

    BEGIN CATCH
    -- No action for if xp_logininfo fails
       END CATCH
       
 SELECT @CurrentRow = @CurrentRow + 1   
 
   END 
   
-- Display final results
SELECT  @@servername AS Servername
    , [PERMISSION_PATH] AS WindowsGroup
    , Account_Name
    , Mapped_Login_Name
    , Account_Type
    , Account_Privilege
FROM @SqlGroupMembership ORDER BY [PERMISSION_PATH], [ACCOUNT_NAME]

Index Fragmentation Status (includes Partitioned Tables/Indexes) Script

Published on SQLServerCentral.com today, another effort from myself....

SSC : Index Fragmentation Status (includes Partitioned Tables/Indexes)

July 2010 - Have updated to include a handy ALTER INDEX , REBUILD command...

SELECT   SCHEMA_NAME(o.schema_id) AS SchemaName               
            ,OBJECT_NAME(o.object_id) AS TableName
            ,i.name  AS IndexName
            ,i.type_desc AS IndexType
            ,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS Partitioned
            ,COALESCE(fg.name ,fgp.name) AS FileGroupName
            ,p.partition_number AS PartitionNumber
            ,p.rows AS PartitionRows
            ,dmv.Avg_Fragmentation_In_Percent
            ,dmv.Fragment_Count
            ,dmv.Avg_Fragment_Size_In_Pages
            ,dmv.Page_Count 
            ,CASE WHEN dmv.Page_Count <> 0 THEN p.rows / dmv.Page_Count ELSE 0 END AS Rows_Per_Page
            ,prv_left.value  AS PartitionLowerBoundaryValue
            ,prv_right.value AS PartitionUpperBoundaryValue
            ,CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' WHEN pf.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'NONE' END AS PartitionRange
            ,pf.name        AS PartitionFunction
            ,ds.name AS PartitionScheme
            ,RebuildCommand = 'ALTER INDEX ['+I.name + '] ON [' + SCHEMA_NAME(o.schema_id) + '].[' + OBJECT_NAME(o.object_id) + '] REBUILD WITH(ONLINE = ON);'
FROM sys.partitions AS p WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
            ON i.object_id = p.object_id
            AND i.index_id = p.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
            ON o.object_id = i.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') dmv
            ON dmv.OBJECT_ID = i.object_id
            AND dmv.index_id = i.index_id
            AND dmv.partition_number  = p.partition_number
LEFT JOIN sys.data_spaces AS ds WITH (NOLOCK)
      ON ds.data_space_id = i.data_space_id
LEFT JOIN sys.partition_schemes AS ps WITH (NOLOCK)
      ON ps.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_functions AS pf WITH (NOLOCK)
      ON pf.function_id = ps.function_id
LEFT JOIN sys.destination_data_spaces AS dds WITH (NOLOCK)
      ON dds.partition_scheme_id = ps.data_space_id
      AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups AS fg WITH (NOLOCK)
      ON fg.data_space_id = i.data_space_id
LEFT JOIN sys.filegroups AS fgp WITH (NOLOCK)
      ON fgp.data_space_id = dds.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left WITH (NOLOCK)
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right WITH (NOLOCK)
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0  
ORDER BY
     SchemaName
    ,TableName
    ,IndexName
    ,PartitionNumber

update!!!

A helpful forum participant has turned it into a much faster CTE!

;WITH IndexStats AS
(
        select 
                        object_id,
                        index_id,
                        partition_number,
                        Avg_Fragmentation_In_Percent,
                        Fragment_Count,
                        Avg_Fragment_Size_In_Pages,
                        Page_Count
                from 
                        sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, N'LIMITED')
)
SELECT
             SCHEMA_NAME(o.schema_id) AS SchemaName               
            ,OBJECT_NAME(o.object_id) AS TableName
            ,i.name  AS IndexName
            ,i.type_desc AS IndexType
            ,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS Partitioned
            ,COALESCE(fg.name ,fgp.name) AS FileGroupName
            ,p.partition_number AS PartitionNumber
            ,p.rows AS PartitionRows
            ,dmv.Avg_Fragmentation_In_Percent
            ,dmv.Fragment_Count
            ,dmv.Avg_Fragment_Size_In_Pages
            ,dmv.Page_Count 
            ,prv_left.value  AS PartitionLowerBoundaryValue
            ,prv_right.value AS PartitionUpperBoundaryValue
            ,CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' WHEN pf.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'NONE' END AS PartitionRange
            ,pf.name        AS PartitionFunction
            ,ds.name AS PartitionScheme
           , RebuildCommand = 'ALTER INDEX ['+I.name + '] ON [' + SCHEMA_NAME(o.schema_id) + '].[' + OBJECT_NAME(o.object_id) + '] REBUILD WITH(ONLINE = ON);' 
FROM sys.partitions AS p WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
            ON i.object_id = p.object_id
            AND i.index_id = p.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
            ON o.object_id = i.object_id
INNER JOIN IndexStats dmv
            ON dmv.OBJECT_ID = i.object_id
            AND dmv.index_id = i.index_id
            AND dmv.partition_number  = p.partition_number
LEFT JOIN sys.data_spaces AS ds WITH (NOLOCK)
      ON ds.data_space_id = i.data_space_id
LEFT JOIN sys.partition_schemes AS ps WITH (NOLOCK)
      ON ps.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_functions AS pf WITH (NOLOCK)
      ON pf.function_id = ps.function_id
LEFT JOIN sys.destination_data_spaces AS dds WITH (NOLOCK)
      ON dds.partition_scheme_id = ps.data_space_id
      AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups AS fg WITH (NOLOCK)
      ON fg.data_space_id = i.data_space_id
LEFT JOIN sys.filegroups AS fgp WITH (NOLOCK)
      ON fgp.data_space_id = dds.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left WITH (NOLOCK)
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right WITH (NOLOCK)
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0  
ORDER BY
Avg_Fragmentation_In_Percent DESC
            ,SchemaName
    ,TableName
    ,IndexName
    ,PartitionNumber

Column Size Checker

This is a utility script I wrote to show how well your column sizes suit your data (or vice versa)..
It has been published on SQLServerCentral.com  today.

SSC : Column Size Checker

March 2011 Update : Putting the function here too now as SSC exclusivity period over...

/*
Script    : Column Size Checker
Version   : 1.0 (March 2010)
Author    : Richard Doering
Web       : http://sqlsolace.blogspot.com
*/

SET NOCOUNT ON 
SET ANSI_WARNINGS ON
DECLARE @SCHEMA  VARCHAR(50)
DECLARE @TABLE  VARCHAR(50)

SET @SCHEMA = ''
SET @TABLE  = ''

DECLARE  @CURRENTROW INT
DECLARE  @TOTALROWS INT
DECLARE  @COLUMNMAXSIZE INT
DECLARE  @COLUMNMINSIZE INT
DECLARE  @SQLSTRING NVARCHAR(MAX)
DECLARE  @PARAMETER NVARCHAR(500);
DECLARE  @TABLEDETAILS 
  TABLE(UNIQUEROWID     INT   IDENTITY ( 1,1 ),
     TABLE_SCHEMA    VARCHAR(255),
     TABLE_NAME      VARCHAR(255),
     COLUMN_NAME     VARCHAR(255),
     COLUMN_TYPE     VARCHAR(255),
     TABLE_ROWS      BIGINT,
     MAX_LENGTH      INT,
     DATA_MIN_LENGTH INT,
     DATA_MAX_LENGTH INT)
       
INSERT INTO @TABLEDETAILS
          (TABLE_SCHEMA,
           TABLE_NAME,
           COLUMN_NAME,
           COLUMN_TYPE,
           TABLE_ROWS,
           MAX_LENGTH)

SELECT SCHEMA_NAME(O.SCHEMA_ID)  AS TABLE_SCHEMA,
      OBJECT_NAME(O.OBJECT_ID)  AS TABLE_NAME,
      C.NAME                    AS COLUMN_NAME,
      T.NAME                    AS COLUMN_TYPE,
      R.SUMROWS   AS TABLE_ROWS,
      C.MAX_LENGTH
FROM   SYS.TABLES O
INNER JOIN SYS.COLUMNS C
       ON C.OBJECT_ID = O.OBJECT_ID
INNER JOIN SYS.TYPES T
       ON C.SYSTEM_TYPE_ID = T.SYSTEM_TYPE_ID
      AND T.NAME IN ('CHAR','VARCHAR','NCHAR','NVARCHAR')
INNER JOIN (SELECT OBJECT_ID, SUM(ROWS) AS SUMROWS FROM SYS.PARTITIONS WHERE INDEX_ID IN (0,1) GROUP BY OBJECT_ID) R
  ON R.OBJECT_ID = O.OBJECT_ID
      
WHERE SCHEMA_NAME(O.SCHEMA_ID)  <> 'sys'
AND OBJECT_NAME(O.OBJECT_ID) = CASE WHEN @TABLE = '' THEN OBJECT_NAME(O.OBJECT_ID) ELSE @TABLE END
AND SCHEMA_NAME(O.SCHEMA_ID) = CASE WHEN @SCHEMA = '' THEN SCHEMA_NAME(O.SCHEMA_ID) ELSE @SCHEMA END
                                
SELECT @TOTALROWS = COUNT(*) FROM   @TABLEDETAILS
SELECT @CURRENTROW = 1
WHILE @CURRENTROW <= @TOTALROWS   
BEGIN     
SET @COLUMNMAXSIZE = 0     
SET @COLUMNMINSIZE = 0  
SELECT @SQLSTRING = 'SELECT @COLUMNSIZEMIN = MIN(LEN([' + COLUMN_NAME + '])) ,@COLUMNSIZEMAX = MAX(LEN([' + COLUMN_NAME + '])) FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] WITH (NOLOCK)'     FROM   @TABLEDETAILS     WHERE  UNIQUEROWID = @CURRENTROW                               
SET @PARAMETER = N'@COLUMNSIZEMAX INT OUTPUT,@COLUMNSIZEMIN INT OUTPUT';          
EXECUTE SP_EXECUTESQL       @SQLSTRING 
                          , @PARAMETER 
                          , @COLUMNSIZEMIN = @COLUMNMINSIZE OUTPUT   
                          , @COLUMNSIZEMAX = @COLUMNMAXSIZE OUTPUT      
UPDATE @TABLEDETAILS     
SET    DATA_MAX_LENGTH = ISNULL(@COLUMNMAXSIZE,0)  , DATA_MIN_LENGTH = ISNULL(@COLUMNMINSIZE,0)   
WHERE  UNIQUEROWID = @CURRENTROW                               

SET @CURRENTROW = @CURRENTROW + 1   
END      

SELECT   TABLE_SCHEMA
        ,TABLE_NAME
        ,TABLE_ROWS
        ,COLUMN_NAME
        ,COLUMN_TYPE
        ,CASE MAX_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(CHAR(10),MAX_LENGTH) END AS COLUMN_MAX_LENGTH
        ,DATA_MIN_LENGTH 
        ,DATA_MAX_LENGTH 
FROM @TABLEDETAILS 
ORDER BY 1, 2, 3

Character Padding Function

This is a function I've written to align fixed character strings to a fixed size.output.It has been published on SQLServerCentral.com  today.

SSC : Character Padding Function

March 2011 Update : Putting the function here too now as SSC exclusivity period over...

/*
Script  : Character Padding Function
Version : 1.0 (April 2010)
Author  : Richard Doering
Web     : http://sqlsolace.blogspot.com
*/
CREATE FUNCTION dbo.CharPad (
  @Input VARCHAR(255)
 ,@OutputWidth INT
 ,@OutputAlign VARCHAR(5)
 ,@PadCharacter CHAR(1) )
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @Output VARCHAR(255)
DECLARE @InputWidth INT

SET @InputWidth = LEN(@Input)

IF @InputWidth > @OutputWidth
 BEGIN 
  IF @OutputAlign = 'LEFT'
   BEGIN
   SET @Output = LEFT(@Input,@OutputWidth)
   END
  IF @OutputAlign = 'RIGHT'
   BEGIN
   SET @Output = RIGHT(@Input,@OutputWidth)
   END
 END

IF @InputWidth < @OutputWidth 
 BEGIN 
  IF @OutputAlign = 'RIGHT'
   BEGIN
   SET @Output = REPLICATE(@PadCharacter, @OutputWidth - @InputWidth ) + @Input
   END
  IF @OutputAlign = 'LEFT'
   BEGIN
   SET @Output =@Input+ REPLICATE(@PadCharacter, @OutputWidth - @InputWidth )
   END
 END

IF @InputWidth = @OutputWidth 
 SET @Output = @Input


RETURN (@Output)
END
GO

Linked Server Connectivity Test Procedure

My latest submission has been published on SQLServerCentral.com as script of the day today :)

SSC : Linked Server Connectivity Test

March 2011 Update : Putting code here after exclusivity period elapsed -

/*
Script  : Linked Server Connectivity Checker
Version : 1.0 (March 2010)
Author  : Richard Doering
Web     : http://sqlsolace.blogspot.com
*/
CREATE SCHEMA [Utils] AUTHORIZATION [dbo]
GO

CREATE PROCEDURE [Utils].[LinkedServerTest] 
 @ServerName SYSNAME
,@emailProfile VARCHAR(255) = ''
,@emailTo VARCHAR(255) = ''
AS 
BEGIN
DECLARE @Test BIT

BEGIN TRY
EXEC @Test= sys.sp_testlinkedserver @servername 

PRINT 'Sucessfully connected to ' + CAST(@servername as VARCHAR(30))
END TRY

BEGIN CATCH
PRINT 'Failed to connect to ' + CAST(@servername as VARCHAR(30))

IF (@emailProfile <> '') AND (@emailTo <> '')
BEGIN
DECLARE @emailSubject VARCHAR(255)
DECLARE @emailBody VARCHAR(8000)

SET @emailSubject = 'Linked Server Connnection Failure : ' + @servername + ' cannot be accessed from ' + @@SERVERNAME
SET @emailBody = @emailSubject

EXEC msdb.dbo.sp_send_dbmail 
 @profile_name= @emailProfile
, @recipients=@emailTo
, @body=@emailBody
, @subject=@emailSubject
, @importance='High'
END

RAISERROR ('Linked Server Failure', 16, 1, @emailSubject) WITH LOG

END CATCH

END

GO

SQL 2008 : Uncompressed Objects Procedure

SQL Server Central have published my latest procedure as 'script of the day'.

The procedure is based arond SQL 2008 compression functionality and provides >

1. Lists of tables & indexes without compression
2. Lists of tables & indexes not using the desired compression (e.g. ROW when you've specified a compression type of PAGE)
3. TSQL commands to compress the database objects.

SSC : SQL 2008 : Uncompressed Objects Procedure


March 2011 Update : Putting the function here too now as SSC exclusivity period over...

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
*/

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)
DECLARE @missingindexcompressiontsql VARCHAR(MAX)
DECLARE @missingindextablelist VARCHAR(MAX)
DECLARE @missingindexindexlist VARCHAR(MAX)
DECLARE @missingcompressiontablecount INT
DECLARE @missingcompressionindexcount INT
DECLARE @changeindexcompressiontsql VARCHAR(MAX)
DECLARE @changeindextablelist VARCHAR(MAX)
DECLARE @changeindexindexlist VARCHAR(MAX)
DECLARE @changecompressiontablecount INT
DECLARE @changecompressionindexcount INT
DECLARE @CurrentRow INT
DECLARE @TotalRows INT
DECLARE @Objecttype VARCHAR(10)
DECLARE @objectname VARCHAR(100)
DECLARE @command VARCHAR(1000)
DECLARE @emailsubject VARCHAR(100)
DECLARE @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

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