I've recently been researching SQL Server database compression in an effort to save disk space in my company's production and non-production environments. Much of our database contains text that would compress extremely well.
SQL Server offers the stored procedure sp_estimate_data_compression_savings to provide an estimate of what storage savings data compression would offer your tables and indexes. I'd like an at-a-glance view of all tables, but it unfortunately can only be run on a single table at a time and not an entire database.
Below you can find a SQL script that collects all of the user tables in a database, executes the data compression estimate stored procedure on them, and returns the results in a temp table.
use MyDatabase go if (object_id('tempdb.dbo.##Tables') is not null) drop table ##Tables create table ##Tables ( object_name sysname, schema_name sysname, index_id int, partition_number int, size_with_current_compression_setting bigint, size_with_requested_compression_setting bigint, sample_size_with_current_compression_setting bigint, sample_size_with_requested_compression_setting bigint ) set nocount on -- Collect all tables declare @tableName sysname declare @errorText varchar(MAX) declare curTables cursor for select name from sys.tables where type = 'U' open curTables fetch next from curTables into @tableName while @@fetch_status = 0 begin begin try insert into ##Tables exec sp_estimate_data_compression_savings 'dbo', @tableName, null, null, 'PAGE' end try begin catch set @errorText = @tableName + ' : ' + error_message() print @errorText end catch fetch next from curTables into @tableName end close curTables deallocate curTables set nocount off select * from ##Tables