Feb 3, 2011

Compress tables

create proc dbo.stp_compress_tables (
    @compression_type varchar(30) = 'PAGE', --(NONE, ROW, PAGE)
    @schema_name sysname = null,
    @table_name sysname = null,
    @index_name sysname = null
)
as
declare
    @sql_string nvarchar(4000),
    @edition varchar(30),
    @version varchar(10);
set @table_name = coalesce(quotename(@schema_name) + '.' + quotename(@table_name), null);

;with a as (
 select
        edition = convert(varchar, SERVERPROPERTY ('Edition')),
        version = convert(varchar, SERVERPROPERTY('ProductVersion'))
)
select
 @edition = edition,
 @version = left(version, charindex('.', version)-1)
from a;

if @edition like 'Enterprise Edition%' or @edition like 'Developer Edition%' and @version >= 10
begin
    ;with cte as
    (
        select
   table_name = quotename(schema_name(tbl.schema_id)) + '.' + quotename (tbl.name),
            index_name = idx.name
        from
            sys.tables as tbl
        inner join
            sys.indexes as idx on idx.object_id = tbl.object_id
        inner join
            sys.partitions as p on p.object_id=cast(tbl.object_id as int) and p.index_id=idx.index_id
        where
            @compression_type <> (case p.data_compression
                                    when 0 then 'none'
                                    when 1 then 'row'
                                    when 2 then 'page'
                                end)
    )
    select table_name, index_name
        into #t1
    from cte
    where (@table_name is null or @table_name = table_name)
  and (@index_name is null or @index_name = index_name)
  
 set @table_name = '';
 set @index_name = '';

    declare c cursor static
    for
        select table_name, index_name from #t1;
          
    open c;
    fetch next from c into @table_name, @index_name;
    while @@FETCH_STATUS = 0
    begin
  
 
 if @index_name is null --it's a Heap 
  set @sql_string = 'ALTER TABLE @table_name REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = @compression_type)';  
 else --it's an Clustered or Non-Clustered index
  set @sql_string = 'ALTER INDEX @index_name ON @table_name REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = @compression_type)';
 
        set @sql_string = REPLACE(@sql_string, '@table_name', @table_name)
        set @sql_string = REPLACE(@sql_string, '@index_name', coalesce(@index_name,''))
        set @sql_string = REPLACE(@sql_string, '@compression_type', @compression_type)
     
        print @sql_string;
        exec sp_executesql @sql_string;
     
        fetch next from c into @table_name, @index_name; 
    end
    close c;
    deallocate c;
   
end
else
begin

 select 'This sql server edition/version isn''t compatible with data compressione feature.' as Warning, @edition as Edition, @version as Version; 
end;
go

No comments:

Post a Comment

Hi,

Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.