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:
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
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.