Compress All Tables in Database

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

Index Information

USE: To display index information.

SET NOCOUNT ON

SELECT DB_NAME(IPS.database_id) AS [database_name]
     , OBJECT_NAME(IPS.object_id, IPS.database_id) AS [object_name]
     , QUOTENAME(I.name) [index_name]
     , CASE WHEN avg_fragmentation_in_percent &lt; 10 THEN &#039;LOW&#039;
            WHEN avg_fragmentation_in_percent &lt; 30 THEN &#039;MEDIUM&#039;
            WHEN avg_fragmentation_in_percent &lt; 50 THEN &#039;HIGH&#039;
            ELSE &#039;EXTREME&#039; END [fragmentation_indicator]
     , IPS.index_id
     , IPS.index_type_desc
     , IPS.avg_fragmentation_in_percent
     , IPS.fragment_count
     , IPS.avg_fragment_size_in_pages
     , IPS.page_count
  FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , &#039;LIMITED&#039;) AS IPS
 INNER JOIN sys.indexes I  ON IPS.object_id = I.object_id 
   AND IPS.index_id = I.index_id
 ORDER BY [object_name], [index_name]
GO

Table Constraints

USE: To display all table constraints from a database.

SET NOCOUNT ON

SELECT A.name [TableName]
     , B.name [ColumnName]
     , C.name [ConstraintName]
     , C.[type] [ConstraintType]
     , A.crdate
  FROM sysobjects A
 INNER JOIN sys.columns B ON A.id = B.object_id
 INNER JOIN sys.key_constraints C ON A.id = C.parent_object_id
 WHERE A.Xtype = 'U'
   AND B.is_identity = 1
 ORDER BY 1
GO

Database Information

USE: To display database detail information.

SELECT @@SERVERNAME [ServerName],
       MF.data_space_id,
       MF.database_id,
       MF.[type],
       MF.[type_desc],
       MF.[name] [DBName],
       MF.[physical_name] [FilePath],
       MF.[state_desc],
       CAST((MF.[size] * 8.0) / 1024.0 AS numeric(19,2)) [FileSize (MB)],
       MF.[max_size],
       MF.[growth],
       DB.[create_date],
       DB.[compatibility_level],
       DB.[collation_name],
       DB.[user_access_desc],
       DB.[state_desc],
       DB.[recovery_model]
  FROM sys.master_files MF
 INNER JOIN sys.databases DB ON MF.database_id = DB.database_id
  LEFT JOIN sys.data_spaces DS ON MF.data_space_id = DS.data_space_id
  LEFT JOIN sys.database_files DF ON DS.name = DF.name
GO

Database Size

USE: To display database file size information at the server level.

SELECT @@SERVERNAME [ServerName],
       MF.database_id,
       DB.[name] [DBName],
       SUM(CAST((MF.[size] * 8.0) / 1024.0 AS numeric(19,2))) [Total Space Usage (MB)]
  FROM sys.master_files MF
 INNER JOIN sys.databases DB ON MF.database_id = DB.database_id
 GROUP BY MF.database_id, DB.[name]
 ORDER BY MF.database_id
 GO

Capture sp_who2

USE: To capture the result from executing sp_who2 system stored procedure.

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#SP_WHO2') IS NOT NULL
   DROP TABLE #SP_WHO2
GO

CREATE TABLE #SP_WHO2 (
       SPID varchar(128)
     , [Status] char(12)
     , [Login] varchar(128)
     , [HostName] varchar(128)
     , [BlkBy] varchar(128)
     , [DBName] varchar(128)
     , [Command] varchar(128)
     , [CPUTime] varchar(128)
     , [DiskIO] varchar(128)
     , LastBatch varchar(128)
     , ProgramName varchar(128)
     , SPID2 varchar(128)
     , REQUESTED varchar(128))

INSERT #SP_WHO2
EXEC SP_WHO2
GO

Database File Growth

USE: To find out recent database files growth.

IF(SELECT CONVERT(int,value_in_use) FROM sys.configurations WHERE [name] = 'default trace enabled' ) = 1 
BEGIN
  declare @curr_tracefilename varchar(500) ; 
  declare @base_tracefilename varchar(500) ; 
  declare @indx int ;

  SELECT @curr_tracefilename = [path] 
    FROM sys.traces where is_default = 1 ;
    
    SET @curr_tracefilename = REVERSE(@curr_tracefilename);
    
    SELECT @indx  = PATINDEX('%%', @curr_tracefilename) ;

    SET @curr_tracefilename = REVERSE(@curr_tracefilename) ;
    SET @base_tracefilename = LEFT( @curr_tracefilename,LEN(@curr_tracefilename) - @indx) + 'log.trc' ;  

  SELECT (DENSE_RANK() OVER(ORDER BY StartTime DESC))%2 AS [l1],
         CONVERT(int, EventClass) [EventClass],
         DatabaseName,
         [Filename],
         (Duration/1000) [Duration],
         StartTime,
         EndTime,
         (IntegerData*8.0/1024) [ChangeInSize]
    FROM ::fn_trace_gettable( @base_tracefilename, default ) 
   WHERE EventClass >= 92 AND
         EventClass <= 95 AND
         ServerName = @@SERVERNAME AND
         DatabaseName = DB_NAME()  
   ORDER BY StartTime DESC;   
END
GO

Default Trace

DECLARE @TracePath nvarchar(256)

SELECT @TracePath =  [path]
  FROM master.sys.traces

SELECT *
  FROM ::fn_trace_gettable (@TracePath,default)

SELECT T.EventID
     , T.ColumnID
     , E.Name [EventDescription]
     , C.Name [ColumnDescription]
  FROM ::fn_trace_geteventinfo(1) T
 INNER JOIN sys.trace_events E ON T.EventID = E.trace_event_id
 INNER JOIN sys.trace_columns C ON T.columnid = C.trace_column_id

SELECT T.EventID
     , E.Name [EventDescription]
     , COUNT(*) [DataColumnCount]
  FROM ::fn_trace_geteventinfo(1) T
 INNER JOIN sys.trace_events E ON T.EventID = E.trace_event_id
 GROUP BY T.EventID, E.Name