SQL Server - Estimate Data Compression Savings for All Tables in a Database

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