Data Compression

,

EXEC [dbo].[spCompression]
This will compress all tables and indexes for the current database.
EXEC [dbo].[spCompression] @Execute =0
This will show the tables and indexes that will be compressed, without actually executing the compress command.  USE THIS to see what will be compressed in your database.
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 @RowCount		int
		,@OnRow			int =0
		,@CMD			nvarchar(MAX)
		,@Status		varchar(max)
		,@SchemanName	varchar(255)
		,@TableName		varchar(255)
		,@IndexName		varchar(255)
		,@IsPrimaryKey	BIT

	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

Rate

3.33 (6)

Share

Share

Rate

3.33 (6)