How to get a list of all the tables along with space consumption in a database


USE database
GO

WITH t AS
(
SELECT
SCHEMA_NAME(t.schema_id) AS schema_name
,t.name AS table_name
,SUM(CASE WHEN p.index_id IN(0,1) AND a.type_desc = ‘IN_ROW_DATA’ THEN p.rows ELSE 0 END) AS rows
,SUM(CAST((a.total_pages * 8.00) / 1024 AS DECIMAL(9,2))) AS MB
,SUM(a.total_pages) AS pages
,ds.name AS location
FROM
sys.tables AS t
INNER JOIN sys.partitions AS p ON t.OBJECT_ID = p.OBJECT_ID
INNER JOIN sys.allocation_units AS a ON p.hobt_id = a.container_id
INNER JOIN sys.data_spaces AS ds ON a.data_space_id = ds.data_space_id
WHERE t.name LIKE ‘%’
GROUP BY SCHEMA_NAME(t.schema_id), t.name, ds.name
)

SELECT schema_name, table_name, rows, MB, pages, location,getdate() as transaction_date
FROM t
ORDER BY
MB DESC