Tuesday 24 October 2017

SQL Server :: T-SQL command that will determine / find the biggest tables in a database

It is very common to deal with situation where you have thousands of tables and want to find the biggest tables in your database. Following mentioned script will return row count, total consumed size per table so we can apply filters too to get only those table which satisfy our criteria ( for example , table which have more than 1 million rows) .

Please change database context accordingly.

**********************************************

SELECT
    t.NAME AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages,
    sum(a.used_pages) as UsedPages,
    sum(a.data_pages) as DataPages,
    (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 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
WHERE
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND  
    i.index_id <= 1
GROUP BY
    t.NAME, i.object_id, i.index_id, i.name
ORDER BY

    (sum(a.data_pages) * 8) / 1024 desc

Hope it will help you .

Brgds,

Chhavinath Mishra
Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)