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 ; GO |
