Show data and disk use breakdown by table
In SSMS, right-click on the database and go to "Reports", "Standard Reports", "Disk Usage by Table". It will tell you the total size, the data size, the index size, and the unused size for each table (as well as the row count).
It's been answered on Stack Overflow:
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
The query linked to, and copied by, @Nelson is inaccurate: it ignores Indexed Views, Full Text Indexes, XML Indexes, etc.
If you want a query that will include everything without executing sp_spaceused
via sp_MSForEachTable
, then I have already posted two variations of it (one here on DBA.StackExchange and the other on StackOverflow) so I won't copy them here:
Per each Table / Indexed View: sp_spaceused - How to measure the size in GB in all the tables in SQL
Per each Index: space usage on sys.allocation_units and sp_spaceused