Data Compression

USE [YourDatabaseNameHere]
GO
/****** Object:  StoredProcedure [dbo].[spCompression]    Script Date: 10/9/2015 11:30:55 AM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[spCompression]
(@Execute INT =1 )
/*-----------------------------------------------------------------------------------------
       Date:2015.03.17
  Developer:Eric S Blake
Description:System Util to keep compression to page.
Version:1.00
-------------------------------------------------------------------------------------------
Modifications
-------------------------------------------------------------------------------------------
       Date:yyyy/mm/dd
  Developer:your name
Description:description of mod
    Version:Version##
-------------------------------------------------------------------------------------------*/as
begin
DECLARE @TablesCompressed INT=0
,@IndexesCompressed INT=0
,@ExecuteNow INT = 1 -- 0-Test, 1=Execute now.

Set NOCOUNT ON

SET @ExecuteNow=@Execute

BEGIN TRY
DROP TABLE [#TableCompress]
END TRY
BEGIN CATCH
--
END CATCH
BEGIN TRY
DROP TABLE [#IndexCompress]
END TRY
BEGIN CATCH
--
END CATCH
BEGIN TRY
DROP TABLE [#CompressCommands] -- Depreciated....
END TRY
BEGIN CATCH
--
END CATCH


CREATE TABLE [#TableCompress](
[RID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[SchemaName] [varchar](255) NOT NULL,
[TableName] [varchar](255) NOT NULL,
) 

CREATE TABLE [#IndexCompress](
[RID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[SchemaName] [varchar](255) NOT NULL,
[TableName] [varchar](255) NOT NULL,
[IndexName] [varchar](255) NOT NULL,
[IsPrimaryKey] bit not null,
) 

INSERT INTO [#TableCompress] ([SchemaName],[TableName] )
select s.name [SchemaName], t.name [TableName]
from sys.tables t
  join sys.schemas s on t.schema_id = s.schema_id
  join sys.indexes i on t.object_id = i.object_id
  join sys.partitions p on (i.object_id = p.object_id and i.index_id = p.index_id)
where t.type = 'U' and i.name is null -- Base - Tables 
and p.data_compression_desc='None'
and t.name not like 'sys%'
order by 1, 2

INSERT INTO [#IndexCompress] ([SchemaName],[TableName],[IndexName],[IsPrimaryKey]  )
select s.name [SchemaName], t.name [TableName], i.name [IndexName]
,isnull([i].[is_primary_key],0)AS [IsPrimaryKey]
  from sys.tables t
  join sys.schemas s on t.schema_id = s.schema_id
  join sys.indexes i on t.object_id = i.object_id
  join sys.partitions p on (i.object_id = p.object_id and i.index_id = p.index_id)
where t.type = 'U' and i.name is not null
and p.data_compression_desc='None'
and t.name not like 'sys%'
order by 1, 2,  3

-- TEMP tables loaded....
-- EXECUTE Commands....
DECLARE @RowCountint
,@OnRowint =0
,@CMDnvarchar(MAX)
,@Statusvarchar(max)
,@SchemanNamevarchar(255)
,@TableNamevarchar(255)
,@IndexNamevarchar(255)
,@IsPrimaryKeyBIT

SET @Status='Starting Table/Index Compression...' + CHAR(10)
RAISERROR (@Status, 0, 1) WITH NOWAIT

SELECT @RowCount=COUNT(*) FROM [#TableCompress] 
SET @Status='Table COMPRESSION There are ' + CAST(@RowCount as varchar(10)) + ' Tables to Compress'
RAISERROR (@Status, 0, 1) WITH NOWAIT
IF(@RowCount>0) SELECT CHAR(9) + [SchemaName] + '.' +[TableName]FROM [#TableCompress]
SET @OnRow=1
WHILE (@OnRow<=@RowCount)
BEGIN
Select @SchemanName =[SchemaName],@TableName=[TableName]FROM [#TableCompress] where [RID]=@OnRow
SET @Status='Compressing Table ' + QUOTENAME(@SchemanName)+'.'+QUOTENAME(@TableName);
SET @Status+=' ' + CAST(@OnRow as varchar(10)) +' of ' + cast(@RowCount as varchar(10))
RAISERROR (@Status, 0, 1) WITH NOWAIT
SET @CMD='ALTER TABLE [@S].[@T] REBUILD PARTITION = ALL WITH  (DATA_COMPRESSION=PAGE)'
SET @CMD=REPLACE(@CMD,'@S',@SchemanName)
SET @CMD=REPLACE(@CMD,'@T',@TableName)
SET @Status=CHAR(9)+@CMD
RAISERROR (@Status, 0, 1) WITH NOWAIT
IF(@ExecuteNow=1) EXEC (@CMD)
SET @TablesCompressed +=1;
SET @OnRow+=1;
END
SET @Status='Table Processing Complete...' + CHAR(10)
RAISERROR (@Status, 0, 1) WITH NOWAIT




SELECT @RowCount=COUNT(*) FROM [#IndexCompress]
SET @Status='Index COMPRESSION There are ' + CAST(@RowCount as varchar(10)) + ' Indexes to Compress'
RAISERROR (@Status, 0, 1) WITH NOWAIT
IF(@RowCount>0) SELECT CHAR(9) + [SchemaName] + '.' +[TableName]+'.'+[IndexName]FROM [#IndexCompress]
SET @OnRow=1
WHILE (@OnRow<=@RowCount)
BEGIN
Select @SchemanName =[SchemaName]
,@TableName=[TableName]
,@IndexName=[IndexName]
,@IsPrimaryKey=[IsPrimaryKey]FROM [#IndexCompress] where [RID]=@OnRow
SET @Status='Compressing Index ' + QUOTENAME(@SchemanName)+'.'+QUOTENAME(@TableName) +'.'+QUOTENAME(@IndexName);
SET @Status+=' ' + CAST(@OnRow as varchar(10)) +' of ' + cast(@RowCount as varchar(10))
RAISERROR (@Status, 0, 1) WITH NOWAIT

SET @CMD='ALTER INDEX [@I] ON [@S].[@T] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE)'

SET @CMD=REPLACE(@CMD,'@S',@SchemanName)
SET @CMD=REPLACE(@CMD,'@T',@TableName)
SET @CMD=REPLACE(@CMD,'@I',@IndexName)
SET @Status=CHAR(9)+@CMD
RAISERROR (@Status, 0, 1) WITH NOWAIT
IF(@ExecuteNow=1) EXEC (@CMD)
SET @TablesCompressed +=1;
SET @OnRow+=1;
END
SET @Status='Index Processing Complete...' + CHAR(10)
RAISERROR (@Status, 0, 1) WITH NOWAIT

BEGIN TRY
DROP TABLE [#TableCompress]
END TRY
BEGIN CATCH
--
END CATCH
BEGIN TRY
DROP TABLE [#IndexCompress]
END TRY
BEGIN CATCH
--
END CATCH
END