Get the row count of all table in a database

We have a database in that data based we have around 500 table and want to get the number of rows in all table.  We need to write query to get the number of records from all table as below. For the demo here we are using the AdventureWorks2019.

Let’s see.

We need to write the query as below

SELECT 'Sales.SalesTaxRate' AS TableName                        , COUNT(*) AS RC FROM Sales.SalesTaxRate WITH (NOLOCK) SELECT 'Sales.PersonCreditCard' AS TableName                        , COUNT(*) AS RC FROM Sales.PersonCreditCard WITH (NOLOCK) SELECT 'Person.PersonPhone' AS TableName                        , COUNT(*) AS RC FROM Person.PersonPhone WITH (NOLOCK) SELECT 'Sales.SalesTerritory' AS TableName                        , COUNT(*) AS RC FROM Sales.SalesTerritory WITH (NOLOCK)  And so on

It is very tedious task to get the list of table and again save this result in presentable format.

With the help of below query our life will be easy. In a single script we will get the count of records for all table in the presentable format.

DECLARE @QueryString NVARCHAR(MAX) ; SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')                       + 'SELECT '                       + '''' + SCHEMA_NAME(sOBJ.schema_id)                       + '.' + sOBJ.name + '''' + ' AS TableName                       , COUNT(*) AS RC FROM '                       + SCHEMA_NAME(sOBJ.schema_id)                       + '.' + sOBJ.name + ' WITH (NOLOCK) ' FROM sys.objects AS sOBJ WHERE sOBJ.type = 'U' ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;  EXEC sp_executesql @QueryString GO