Row Count of all tables - returned in a single dataset

When you need the row count from a table, the most common way is to use count(*). But what if you need the row count for all the tables in your database?  You could use the sp_MSforeachtable stored procedure:

    EXEC sys.sp_MSforeachtable 'select ''?'',count(*) from ?;';

Nice, but the return format is a bit difficult to manage, since it effectively runs the query for as many times as you have tables in your database.  The output might look something like this:


               
          -------------------- -----------
          [dbo].[Product_info] 504

                           
          --------------------- -----------
          [dbo].[database_info] 24

Sure, you could package this to run an insert into a temp table, but still, it's a bit awkward. What we want is a single, elegant query. One that returns a single dataset. Fortunately, we can get the information from the DMV sys.dm_db_partition_stats. One of the columns returned from this DMV is row_count. The one caveat is that row_count is approximate.

Our query will look something like this:


  select p.row_count, tableName = object_name(object_id), * 
   from sys.dm_db_partition_stats as p
inner join sys.sysobjects o 
      on p.object_id = o.id  and o.xtype = N'U'

where p.index_id IN( 0,1) and p.row_count > 0
   order by p.row_count desc;

A table is either a heap, or it has a clustered index. Heaps have an index_id of 0, while clustered indexes have an index_id of 1. They are mutually exclusive.

You can read more about sys.dm_db_partition_stats on MSDN here: https://msdn.microsoft.com/en-us/library/ms187737.aspx.