In absence of a good script for Database compression that includes,

1. Error handlings of all and any unforeseen kind
2. Avoidance of “Sparse columns”
3. Not attempting on already compressed objects
4. Attempt Row compression if Page  compression fails
5. Proceeding in case of errors without human intervention so the large database compression can be left to happen

I have come up with the following,

/*

Database compression utility 

Script runs in current database context and compresses all tables and indexes. Preferred method is Page compression.

Features over traditional compression
- Error handlings of all and any unforeseen kind
- Avoidance of "Sparse column" containing tables and indexes
- Not attempting on already compressed objects
- Attempt Row compression if Page  compression fails
- Proceeding in case of errors without human intervention so the large database compression can be left to happen
- Progress reporting



sz_zaidi@hotmail.com

*/

--use 




declare @DatabaseName nvarchar(100)
declare @DatabaseSize numeric(20,10)
Declare @objectname as nvarchar(1000)
declare @objecttype nvarchar(100)
Declare @tsqltxt nvarchar(1000)
Declare @tsqltxtFinished nvarchar(1000)
declare @IndexName nvarchar(100)
declare @fetch_outer_cursor int
declare @fetch_inner_cursor int
Declare @Object2compresscount int	-- Total objects to compress
Declare @Objectcount int



Set @Objectcount = 0	-- Progress reporting counter
set @Object2compresscount = (select count(*) FROM [sys].[partitions] (NOLOCK) AS [p] INNER JOIN sys.tables (NOLOCK) AS [t] ON [t].[object_id] = [p].[object_id] INNER JOIN sys.indexes (NOLOCK) AS [i] ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id] INNER JOIN sys.schemas (NOLOCK) AS s ON t.[schema_id] = s.[schema_id] where p.data_compression<>2 and t.name not in (SELECT   distinct   t.name  from        sys.tables (NOLOCK)  t   left join         sys.columns  (NOLOCK) c ON c.object_id = t.object_id  WHERE   		     c.is_sparse =1 ))

Print 'Objects to Compress in the database =' + convert(varchar,@Object2compresscount ) + char(13)

		Declare curTblsIdxs cursor for
		SELECT distinct '[' + s.name + '].[' + [t].[name] + ']' AS [Object], 
		    '[' + [i].[name] + ']' AS [Index],  
		case 
				when p.index_id > 1 then 'Index'
				when p.index_id <=1 then 'Table'
			end ObjectType
			--, p.data_compression_desc		
			--,p.data_compression
		FROM [sys].[partitions] (NOLOCK) AS [p]
		INNER JOIN sys.tables (NOLOCK) AS [t] ON [t].[object_id] = [p].[object_id]
		INNER JOIN sys.indexes (NOLOCK) AS [i] ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id]
		INNER JOIN sys.schemas (NOLOCK) AS s ON t.[schema_id] = s.[schema_id]
		where p.data_compression<>2
		and t.name not in (SELECT   distinct   t.name  from        sys.tables (NOLOCK)  t   left join         sys.columns  (NOLOCK) c ON c.object_id = t.object_id
		 WHERE   		     c.is_sparse =1 )

		open curTblsIdxs
		Fetch next from  curTblsIdxs into @objectname,@Indexname , @objecttype
		set @fetch_inner_cursor = @@FETCH_STATUS

		while @fetch_inner_cursor = 0
 			BEGIN  		
				set @Objectcount = @Objectcount + 1
				set @tsqltxt = ''
				set @tsqltxtFinished = ''
				if @objecttype = 'Table' 
						set @tsqltxt = 'ALTER ' + @objecttype + ' ' + @objectname  
		
				if @objecttype = 'Index'
						set @tsqltxt = 'ALTER ' + @objecttype + ' ' + @IndexName + ' On ' + @objectname 
				Begin try 
							--'print char(13) + @Tablename  + ' , Trying page compression.'+ char(13)
							set @tsqltxtFinished = @tsqltxt + ' REBUILD WITH (DATA_COMPRESSION=PAGE);'
							EXECUTE sp_executesql @tsqltxtFinished
							
							--print @tsqltxt+ char(13)
							print '(@Objectcount:' + convert(varchar,@Objectcount)  + ') ' + @objecttype + ': ' + @objectname  + ' page compressed.'+ char(13)
				End try
				begin catch
						print char(13) + 'Error: ' + @objecttype + ' ' + @objectname  + ' Not compressed with page '+ char(13) + ERROR_MESSAGE() --+ char(13) + @tsqltxtFinished 
						set @tsqltxtFinished = ''
						Begin try 			
							set @tsqltxtFinished = @tsqltxt + ' REBUILD WITH (DATA_COMPRESSION=ROW);'
							EXECUTE sp_executesql @tsqltxtFinished
							
							print '(@Objectcount:' + convert(varchar,@Objectcount)  + ') ' + @objecttype + ': ' + @objectname  + ' Row compressed.'+ char(13)
							--print @tsqltxt+ char(13)							
						End try
						Begin Catch
							print char(13) +  'Error: (@Objectcount:' + convert(varchar,@Objectcount)  + ') ' +    @objecttype + ' ' + @objectname  + ' Not compressed with page or row.'+ char(13) + ERROR_MESSAGE() + char(13)  + @tsqltxtFinished 
							set @tsqltxtFinished = ''
						End Catch;
				end catch;
		Fetch next from  curTblsIdxs into @objectname,@Indexname , @objecttype
		set @fetch_inner_cursor = @@FETCH_STATUS
			End 
			Close curTblsIdxs
			Deallocate curTblsIdxs