SQL – Page compression

Compression of data files is one of the familiar tasks in the database maintenance tasks. Data compression of data files is the process of arranging the data inside of the files.
We have 3 different compressions as
 
1. Page compression
2. Row compression
3. Dictionary compression
 
Many times I got a situation of databases without implementation of any compression even in the production systems which motivated me to prepare one handy script for future reference as I have used this script many times while playing with databases including production environments.
Here presenting a dynamic script to convert any database into any compression with comments
 

DECLARE @Database VARCHAR(255)   
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  

DECLARE DatabaseCursor CURSOR
    FOR SELECT  name
        FROM    master.dbo.sysdatabases
        WHERE   name IN ( 'SANDBOX', 'BITEST' )   -- Specify the database names
        ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
    BEGIN  

        SET @cmd =  'DECLARE TableCursor CURSOR FOR SELECT table_catalog 
                  + ''.'' + table_schema + ''.'' 
                  + table_name as tableName FROM ' + @Database
                  + '.INFORMATION_SCHEMA.TABLES 
                    WHERE table_type = ''BASE TABLE'''   

   -- Create table cursor  
        EXEC ( @cmd
            )  
        OPEN TableCursor   

        FETCH NEXT FROM TableCursor INTO @Table   
        WHILE @@FETCH_STATUS = 0   
            BEGIN 
      
                SET @cmd = 'ALTER TABLE  ' + @Table
                         + ' REBUILD WITH (DATA_COMPRESSION =  PAGE) '  -- Row and Dictionary
                EXEC ( @cmd
                    )  

                FETCH NEXT FROM TableCursor INTO @Table   
            END   

        CLOSE TableCursor   
        DEALLOCATE TableCursor  

        FETCH NEXT FROM DatabaseCursor INTO @Database  
    END  
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor 

 
Points to consider:
 
1. Consider the comments section and modify the parameters according to the situation of databases
2. The database will go into lock mode so consider the maintenance window appropriately
3. Consider this script only as a reference and I recommend understanding the logic in-depth before going to use the script
4. I haven’t presented any executed results as I used many times so I am confident on the script that it will work in any environment