Fastest way to find row count of all tables in SQL

Introduction

There comes a time in the life of many 'developers' where they need to juggle with work not exactly in their domain of code development. Times when you need to provide a quick analysis of the database health and growth patterns for say a new business proposal for the client. Below queries can be very useful for such accidental DBAs. For all others, you can just use this query to show off some cool tricks in SQL! :)

Using the Query

Below is the syntax for the query which does exactly what the title suggests.

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 

Results -

Hope this helps.Thank you for reading.

Oh, you still reading this entry! Is the above query giving a problem? Read on if the answer is yes.

Points of Interest

MSDN page about sys.sysindexes says-

Although I have successfully tested this query on SQL Server 2008 R2 (Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)), if you are unable to run the above query, use the following query to get the desired results.

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 

That's it. As simple as it can get. There is much more important and interesting information which can be extracted from sys.sysindexes, and I’ll be writing about the same soon.

Meanwhile,Let me know your feedback on this