SQL Server Stored Procedure to Compress Tables

  1. CREATE PROCEDURE dbo.usp_CompressNonPartitionedTables (  
  2.   @compression_mode VARCHAR(10),  
  3.   @schema VARCHAR(30),  
  4.   @size_thresholdKB INT )  
  5.   
  6. AS  
  7.   
  8. BEGIN  
  9.  set nocount on  
  10.    
  11.  DECLARE @tsql VARCHAR(200)  
  12.  DECLARE @tablename VARCHAR(60)  
  13.  DECLARE @expectedSavingKB BIGINT  
  14.    
  15.  DECLARE cur CURSOR  
  16.  FOR  
  17.  (SELECT DISTINCT t.NAME AS table_name  
  18.  FROM sys.partitions p ,sys.tables t ,sys.schemas s  
  19.  WHERE p.object_id = t.object_id AND p.partition_number = 1 AND t.schema_id = s.schema_id  
  20.   AND s.NAME = @schema AND p.data_compression = 0);  
  21.   
  22.  -- Create results table  
  23.  CREATE TABLE #estimated_savings (  
  24.   [object_name] SYSNAME  
  25.   ,[schema_name] SYSNAME  
  26.   ,[index_id] INT  
  27.   ,[partition_number] INT  
  28.   ,[size_with_current_compression_settingKB] BIGINT  
  29.   ,[size_with_requested_compression_settingKB] BIGINT  
  30.   ,[sample_size_with_current_compression_settingKB] BIGINT  
  31.   ,[sample_size_with_requested_compression_settingKB] BIGINT );  
  32.   
  33.  OPEN cur   
  34.  FETCH NEXT FROM cur INTO @tablename  
  35.  WHILE @@FETCH_STATUS = 0  
  36.  BEGIN  
  37.   INSERT INTO #estimated_savings  
  38.   EXECUTE sp_estimate_data_compression_savings @schema_name = @schema  
  39.   ,@object_name = @tablename  
  40.   ,@index_id = NULL  
  41.   ,@partition_number = NULL  
  42.   ,@data_compression = @compression_mode;  
  43.   
  44.   SELECT @expectedSavingKB =  ([size_with_current_compression_settingKB] -   
  45.                                [size_with_requested_compression_settingKB])  
  46.   FROM #estimated_savings  
  47.   
  48.   IF (@expectedSavingKB > @size_thresholdKB)  
  49.   BEGIN  
  50.    SET @tsql = 'ALTER TABLE ' + @schema + '.[' + @tableName + ']' +  
  51.     ' rebuild WITH( DATA_COMPRESSION = ' + @compression_mode + ' )'  
  52.    EXEC (@tsql)  
  53.   END  
  54.   TRUNCATE TABLE #estimated_savings  
  55.   FETCH NEXT FROM cur INTO @tablename  
  56.  END  
  57.  CLOSE cur  
  58.  DEALLOCATE cur  
  59.  DROP TABLE #estimated_savings  
  60.  set nocount off  
  61. END  
  62. GO