Click here to Skip to main content
14,022,607 members
Click here to Skip to main content
Add your own
alternative version


8 bookmarked
Posted 24 Aug 2014
Licenced CPOL

Fastest way to find row count of all tables in SQL

, 24 Aug 2014
Rate this:
Please Sign up or sign in to vote.
Simple Queries to find row count of all tables in a MS-SQL Database


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.

       I.rows AS [ROWCOUNT] 
FROM   sys.tables AS T 
       INNER JOIN sys.sysindexes AS I 
               ON T.object_id = 
                  AND I.indid < 2 

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-

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

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.

       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


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer
India India
A Qualified Electronics Engineer, a professional web application developer, an accidental SQL DBA and an amateur Photoshop artist!

You may also be interested in...

Comments and Discussions

QuestionThank you - How to make this dynamic and name the DB? Pin
jwilliamsoh15-Mar-16 5:27
memberjwilliamsoh15-Mar-16 5:27 
SuggestionAnother way to do this is.. Pin
Paw Jershauge1-Oct-15 0:05
memberPaw Jershauge1-Oct-15 0:05 
GeneralRe: Another way to do this is.. Pin
Mihir.D.Shah15-Nov-15 18:58
memberMihir.D.Shah15-Nov-15 18:58 
QuestionAnother way... Pin
Paw Jershauge1-Oct-15 0:04
memberPaw Jershauge1-Oct-15 0:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01 | 2.8.190417.4 | Last Updated 24 Aug 2014
Article Copyright 2014 by Mihir.D.Shah
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid