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