Get the Record Count for all the Tables in a Database in SQL Server

Often we take the COUNT of records in a table or many of the times all the tables in a database. We might need to get the COUNT of records in all the tables especially for validation purposes.
 
For instance when you are loading the data into your Staging Database in an incremental fashion, you need to do few checks to make sure that the incremental logic is working fine. As part of this, one of the most basic checks is to first get the COUNT of records from all the tables in Source & Staging Databases and compare the COUNTs.
 
Here is a very simple way to get the COUNT of records from all the tables in a database. Run the following query in the database in which you need to get the COUNTs of all the tables.
 
DECLARE @QueryString NVARCHAR(MAX)SELECT @QueryString = COALESCE(@QueryString + ‘ UNION ALL ‘,) + ‘SELECT ‘ + ”” + TABLE_SCHEMA + ‘.’ + TABLE_NAME + ”” + ‘ AS TableName, COUNT(1) AS RecordCount FROM ‘ + TABLE_SCHEMA + ‘.’ + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’
ORDER BY TABLE_SCHEMA, TABLE_NAMEEXEC Sp_executesql @QueryString

When you run the above query in AdventureWorksDW database then the results will be as shown below (Tables/Counts might slightly vary depending on the version of AdventureWorksDW). 

Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on February 19, 2010, in Code Snippets, Interview Questions, SQL Server, T-SQL, T-SQL Questions, T-SQL Snippets, T-SQL Tips, Tips 'N' Tricks and tagged , , , , . Bookmark the permalink. 3 Comments.

  1. SELECT SCHEMA_NAME(SCHEMA_ID) +’.’+ OBJECT_NAME(id) NAME, rows
    –INTO #TBL2
    FROM sysindexes I INNER JOIN sys.tables T ON I.id = T.object_id
    WHERE indid <= 1

  2. Hi Datta,

    Nice posts..Thanks for sharing your knowledge.

    To get the total number of records in a table, we can use below query too..

    SELECT SCHEMA_NAME(SCHEMA_ID) +’.’+ OBJECT_NAME(id) NAME, rows
    –INTO #TBL2
    FROM sysindexes I INNER JOIN sys.tables T ON I.id = T.object_id
    WHERE indid <= 1

    Thanks ,
    abhIShek

  3. Hi abhIShek,

    Thanks for visiting my blog and the kind words.
    Take a look at the following article, which has few other ways of getting the row count of all the tables in a SQL Server Database: http://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/

    Best Regards,
    Datta

What are your thoughts?

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: