Get Records counts for all tables in a Database

Suppose we want to find the number of records in each table from a data base we need to write the Query to get count of each table. It is some tedious task.

Here we will learn how to create the count script of the all tables in a database.

SELECT DB_NAME() As [Database Name],              obj.name As [Table name],             'Select '''+ obj.name + ''' as [Table Name], count(*) as [Number of records]  From '                                 + DB_NAME() + '.' + SCHEMA_NAME(SCHEMA_ID) + '.'+obj.name           AS [Script generator to get records counts for all tables] FROM    sys.objects obj WHERE   obj.[type] = 'U' ORDER BY obj.name

With the help of this sql script we will get the Database name, table name and the sql script to get the record counts.

  

Now the count script is ready

We need to run one by one and get the result

  

Again it is bit tedious task.

There is another way to get this very easy. Using System Stored procedures  sp_MSforeachtable.

We find these stored procedures in Master Database

  

We will read the value of the stored procedures and store it on the Temp table and finally we get the values from the Temp 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

See the output