Total Number Of Records In each Table

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 as Name_Schema,o.name as Table_Name, p.row_count as Total_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