Fastest way to find the row count of all tables very quickly in sql server

For finding the number of records we generally use the count function. Here I am using AdventureWorksDW2008R2 Database.

SELECT Count(*) As [Number of Records]   FROM [AdventureWorksDW2008R2].[dbo].[FactInternetSales]

  

For finding the total number is use ROW scan due to that It will take much time. See the execution plan.

In this example there are only 60k records. Think if we have millions of record that who much time it will be take.

We can find the row count using below system views.

 Using Catalog View sys.partitions

It Contains a row for each partition of all the tables and most types of indexes in the database. Special index types such as Full-Text, Spatial, and XML are not included in this view. All tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned.

Here is the script to find the row count of all the tables using sys.partitions.For finding the row count of the all table in the database use below script

SELECT T.NAME AS TableName         ,SUM(P.ROWS) AS [RowCount] FROM sys.partitions AS P INNER JOIN sys.tables AS T ON P.OBJECT_ID = T.OBJECT_ID WHERE P.INDEX_ID < 2 GROUP BY T.NAME ORDER BY [RowCount] DESC

  

If we want to find the row count for the single table we need to filter that.

Using DMV sys.dm_db_partition_stats

It returns page and row-count information for every partition in the current database.

With the help of dm_db_Partitio_stats we can get the number of records. See below script.

SELECT T.NAME AS TableName             ,SUM(S.ROW_COUNT) AS [RowCount] FROM sys.dm_db_partition_stats AS S INNER JOIN sys.tables AS T ON S.OBJECT_ID = T.OBJECT_ID WHERE S.INDEX_ID < 2 GROUP BY T.NAME ORDER BY [RowCount] DESC

  

For finding the row count of single table we need to filter that.

Get the Records count using sp_MSforeachtable  SP

We will get the records count of the table in a database using  SP_MSforeach table.

use  AdventureWorksDW2008R2 CREATE TABLE #TempRecordsCounts     ([Table Name] VARCHAR(200) ,      [Number of records] INT) EXEC sp_MSforeachtable 'insert into #TempRecordsCounts select ''?'', count(*) from ?' SELECT  * FROM    #TempRecordsCounts ORDER BY [Number of records] desc DROP TABLE #TempRecordsCounts

For finding the row count of a single table

  

For more details read here: Get Records counts for all tables in a Database

http://bageshkumarbagi-msbi.blogspot.in/2016/06/get-records-counts-for-all-tables-in.html