How to find the largest sql objects in an SQL Server database

Sometimes you may need know how big some tables are, how many rows they have and how much disk space they use.

The following query looks for the biggest tables in size (MB) and number of rows:

SELECT * FROM (

SELECT 

    tbl.NAME AS TableName,

    ind.name as indexName,

    sum(p.rows) as RowCounts,

    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 

    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 

    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB

FROM 

    sys.tables tbl INNER JOIN 

    sys.indexes ind ON tbl.OBJECT_ID = ind.object_id INNER JOIN 

    sys.partitions p ON ind.object_id = p.OBJECT_ID AND ind.index_id = p.index_id INNER JOIN 

    sys.allocation_units a ON p.partition_id = a.container_id

WHERE 

    tbl.NAME NOT LIKE 'dt%' AND

    ind.OBJECT_ID > 255 AND   

    ind.index_id <= 1

GROUP BY     tbl.NAME, ind.object_id, ind.index_id, ind.name 

) as t1  ORDER BY TotalSpaceMB desc ,UsedSpaceMB desc