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

No comments:

Post a Comment