Fastest way to find row count of all tables in SQL


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 -

tmpFEF0_thumb

Hope this helps.Thank you for reading.

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

MSDN page about sys.sysindexes says-

clip_image001[8]Important
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.

SELECT  T.NAME AS [TABLE NAME],I.ROW_count AS [ROWCOUNT]
FROM SYS.TABLES AS T INNER JOIN sys.dm_db_partition_stats  AS
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.

-MDS

0 comments: (+add yours?)

Post a Comment