Find largest table in a SQL Server database.
Monitoring table size over time is very important to the SQL Server Engine. In performance point of view to run queries. At the most optimal and avoid performance issues due to larger table size as it important factor in SQL Server performance There are often many times people ask, how to find the largest tables/objects in a database, so below scripts, will determine which objects in a database are largest
SELECT tbl.NAME AS TableName, 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 tbl INNER JOIN sys.indexes i ON tbl.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 tbl.NAME NOT LIKE 'dt%' AND i.OBJECT_ID > 255 AND i.index_id <= 1 GROUP BY tbl.NAME, i.object_id, i.index_id, i.name ORDER BY TotalSpaceMB desc
You can use another ordering criteria.
Linux/Windows System and SQL Server Database Administrator with more than 4.5 years of experience in Linux Servers administration, Windows Administration, Network Administration, SQL Server Administration, MySQL Administration and SQL Programming.