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.
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_NAME
EXEC Sp_executesql @QueryString
Feel free to leave your comments if you like this post.
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.
Related Articles
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_NAME
EXEC 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).
Feel free to leave your comments if you like this post.
This post first appeared on MyTechnoBook - Datta's Technical Notes..!!, please read the originial post: here
