CODEWITHSUNDEEP
sql-serversql-server-2014
Philip
Philip

Reputation: 2628

Page Level Compression - All Tables

Does anyone know a way to see the Compression level through the Calculate button without having to go through each table 1-by-1?

enter image description here

Upvotes: 0

Views: 1100

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

You can call sp_estimate_data_compression_savings for each table, specifying the desired compression level. Below is an example script that uses a cursor for the task, inserting the results of each table into a table variable and the final results of all tables via a select query.

USE YourDatabase;
SET NOCOUNT ON;
DECLARE @estimated_compression TABLE(
    object_name sysname,
    schema_name sysname,
    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 @schema_name sysname, @object_name sysname;
DECLARE tables CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
    SELECT s.name AS schema_name, t.name AS table_name
    FROM sys.schemas AS s
    JOIN sys.tables AS t ON t.schema_id = s.schema_id;
OPEN tables;
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM tables INTO @schema_name, @object_name;
    IF @@FETCH_STATUS = -1 BREAK;
    INSERT INTO @estimated_compression
        EXEC sp_estimate_data_compression_savings   
             @schema_name = @schema_name
           , @object_name = @object_name
           , @index_id = NULL
           , @partition_number = NULL
           , @data_compression = 'PAGE';
END
CLOSE tables;
DEALLOCATE tables;
SELECT *
FROM @estimated_compression
ORDER BY object_name, schema_name, index_id, partition_number;
GO

Upvotes: 1

Related Questions