Systems Engineering and RDBMS

Another way to count number of records in all tables

Posted by decipherinfosys on January 16, 2009

In our previous posts, we have shown you SQL code to help get a count of the records in all tables in a schema in both SQL Server and Oracle – you can access them here:

1) SQL Server – here and here.

2) Oracle – count number of records in Oracle.

While working yesterday on helping a client who was using SQL Server 2005 and wanted to get a similar list, wrote another one which is even simpler:

SELECT OBJECT_NAME(object_id) as Table_Name, row_count
FROM sys.dm_db_partition_stats
WHERE index_id < 2
ORDER BY row_count DESC;

As we had mentioned with the previous post as well indid = 0 is for the heap and indid = 1 is for clustered indexes. All indid values more than 1 are for non-clustered indexes. So, using the above gives us what we need in terms of the counts. You need to have the VIEW DATABASE STATE permission so that you can query the sys.dm_db_partition_stats DMV.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: