Find row count of all tables in SQL

Find row count of all tables in SQL

Sometimes as a developer or application owner or management team wants to analyse database records, to easily identify database performance first we need to check that how many no of rows stored in database table. The after we can see other things for database performance like index, joins, outer join, conditions etc....

Below is the query which provides no of rows count of all tables of current database.

SELECT T.name AS [TABLE NAME], 
       I.rows AS [ROWCOUNT] 
FROM   sys.tables AS T 
       INNER JOIN sys.sysindexes AS I 
               ON T.object_id = I.id 
                  AND I.indid < 2 
ORDER  BY I.rows DESC 

SELECT T.name      AS [TABLE NAME], 
       I.row_count AS [ROWCOUNT] 
FROM   sys.tables AS T 
       INNER JOIN sys.dm_db_partition_stats AS I 
               ON T.object_id = I.object_id 
                  AND I.index_id < 2 
ORDER  BY I.row_count DESC 

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    p.[Rows],
    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, p.[Rows]
ORDER BY 
     p.[Rows] desc 

Hope, this will help to review database and then you can take action for performance.

Enjoy !!!

:)