Adding compression to a table at row or page level

It is possible to add compression to a table at row or page level. At row level, any row values that can be stored in a smaller data type, will be. At page level, row level compression is used, and any duplicate data is rolled up within the page, creating more space in each page. The gain in disk space (and therefore fewer reads / writes) is to the detriment of CPU utilisation when reading / writing the data. You can check how much space you can save by using row or page compression by using the following stored procedure:

EXEC sp_estimate_data_compression_savings [Schema], [TableName], NULL, NULL, 'ROW'

You can then add the desired compression type to a table as follows:

ALTER TABLE [TableName]

REBUILD WITH (DATA_COMPRESSION = PAGE)

Get physical size and rowcount of all tables in database

The following script will return table name and corresponding physical size and rowcount for each table in a database:

select
    t.NAME as TableName
    ,s.Name as SchemaName
    ,p.rows as RowCounts
    ,sum(a.total_pages) * 8 as TotalSpaceKB
    ,sum(a.used_pages) * 8 as UsedSpaceKB
    ,(sum(a.total_pages) - sum(a.used_pages)) * 8 as UnusedSpaceKB
from
    sys.tables t
    inner join sys.indexes i
       on t.OBJECT_ID = i.object_id
    inner join sys.partitions p
       on i.object_id = p.OBJECT_ID
       and i.index_id = p.index_id
    inner join sys.allocation_units a
       on p.partition_id = a.container_id
    left outer join sys.schemas s
       on t.schema_id = s.schema_id
where
    t.NAME not like 'dt%'
    and t.is_ms_shipped = 0
    and i.OBJECT_ID > 255
group by
    t.Name,
    s.Name,
    p.Rows
order by
    t.Name

Credit: http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database