we are able to get a rough view of tables detail in a database like how many rows, total space, used space and unused space in each table in a SQL Server database through following query:-
SELECT object_name(ix.object_id) as objectName, ix.[name] as indexName, pt.rows AS RowCounts, Sum(au.total_pages) as total_Pages, sum(au.used_pages) as used_Pages, sum(au.data_pages) as data_Pages, (sum(au.total_pages) * 8 ) / 1024 as total_Space_MB, (sum ( au.used_pages) * 8 ) / 1024 as used_Space_MB, (sum(au.data_pages) * 8 ) / 1024 as data_Space_MB FROM sys.indexes ix INNER JOIN sys.partitions pt ON ix.object_id = pt.object_id AND ix.index_id = pt.index_id INNER JOIN sys.allocation_units au ON pt.partition_id = au.container_id GROUP BY ix.object_id, ix.index_id, ix.[name],pt.rows ORDER BY pt.rows desc GO
Output:-
