Quite often in our day today activity we need to check the count of records in each table of the database. So instead of firing count(*) against all the table, we can use the DMV's ie inbuild system procedures or functions to get the desired result.
Code :
select distinct s.name asName_Schema,o.name as Table_Name,p.row_count asTotal_Records from sys.objects o inner join
sys.dm_db_partition_stats p on o.object_id = p.object_id
inner join sys.schemas s on o.schema_id=s.schema_id
where o.type = 'U'
So lets say we want to get the list of tables along with the total records in Adventureworks database.
Below is the output for the same:
| Name-Schema | Table_Name | Total_Records |
| HumanResources | Department | 16 |
| HumanResources | Employee | 290 |
| HumanResources | EmployeeAddress | 290 |
| HumanResources | EmployeeDepartmentHistory | 296 |
| HumanResources | EmployeePayHistory | 316 |
| HumanResources | JobCandidate | 13 |
| HumanResources | Shift | 3 |
|
|
|
|
