Tables row count, total space, used space and unused space

PROBLEM:
You want to find tables row count, total space (KB), used space (KB) and unused space (KB)

SOLUTION:
The following query returns the row count and size of tables in your database:

SELECT T.NAME AS TableName, 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
GROUP BY T.NAME,
         P.ROWS
ORDER BY T.NAME

The following query returns the size of your database (in megabytes):

SELECT sum(reserved_page_count) * 8.0 / 1024
FROM sys.dm_db_partition_stats

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!