T-SQL script to find the highly populated tables in database

Sometimes we need to check which tables of our database is highly populated. Below is the T-SQL Script which we can use to find out that.

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 
—    object_name(i.object_id) 

ORDER BY SUM(p.rows) DESC

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in DBA, SQL Advanced, SQL Server and tagged , . Bookmark the permalink.

27 Responses to T-SQL script to find the highly populated tables in database

  1. Pingback: รับทำ seo รายเดือน

  2. Pingback: Nuestro Mundo

  3. Pingback: botox

  4. Pingback: 바카라사이트

  5. Pingback: ดูบอลผ่านมือถือ

  6. Pingback: Car Crashes Compilation Russia/USA/Europe 2019 HD

  7. Pingback: w88club

  8. Pingback: CBD oil

  9. Pingback: kompas qq

  10. Pingback: Dream Market Deutsch

  11. Pingback: Samsara Market

  12. Pingback: fun 88

  13. Pingback: Slager hengelo

  14. Pingback: i99bet

  15. Pingback: qq online terbaik

  16. Pingback: sahabat qq

  17. Pingback: hondaqq

  18. Pingback: www.1lapakqq.site

  19. Pingback: iphone xr screen replacement

  20. Pingback: ทางเข้า fifa55

  21. Pingback: Cecilia

  22. Pingback: vinyl truck signs

  23. Pingback: distressed american flag tailgate decal

  24. Pingback: http://www.cialisis.org/

  25. Pingback: fowling board decals

  26. Pingback: management training

  27. Pingback: Alexandra

Leave a Reply