SQL Server: Use page level compression

Set nocount on

Declare @SQL Varchar(1000)
Declare @DBOTable Varchar(1000)

Drop table #MyTable

Create Table #MyTable
(
[object_name] Varchar(200),
[schema_name] Varchar(200),
[index_id] INT,
[partition_number] INT,
[size_with_current_compression_setting (KB)] BIGINT,
[size_with_requested_compression_setting (KB)] BIGINT,
[sample_size_with_current_compression_setting (KB)] BIGINT,
[sample_size_with_requested_compression_setting (KB)] BIGINT
);

Declare MyCursor Cursor For
select Name from sys.tables
where schema_id = 1 and parent_object_id = 0
and Type = 'U'

Open MyCursor

Fetch next from MyCursor into @DBOTable

While @@FETCH_STATUS = 0
Begin
--Print @DBOTable
Set @SQL = ' INSERT INTO #MyTable EXEC sp_estimate_data_compression_savings '
Set @SQL = @SQL + ' @schema_name = ''dbo'','
Set @SQL = @SQL + ' @object_name = '''+ @DBOTable + ''','
Set @SQL = @SQL + ' @index_id = NULL, '
Set @SQL = @SQL + ' @partition_number = NULL, '
Set @SQL = @SQL + ' @data_compression = ''PAGE'' ;'

Print @SQL
Exec(@SQL)
Fetch next from MyCursor into @DBOTable
End

Close MyCursor
Deallocate MyCursor

Declare MyCursor Cursor for
select [object_name] from #MyTable
Where [size_with_requested_compression_setting (KB)] < ([size_with_current_compression_setting (KB)] * 0.9)

Open MyCursor

Fetch next from MyCursor into @DBOTable

While @@FETCH_STATUS = 0
Begin
Set @SQL = ' ALTER TABLE dbo.' + @DBOTable + ' REBUILD WITH (DATA_COMPRESSION = PAGE) '
Print @SQL
Exec(@SQL)
Fetch next from MyCursor into @DBOTable
End

Close MyCursor
Deallocate MyCursor

Drop table #MyTable

Set nocount off