SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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



SQL Blog by Rahul Sahay

I have a total experience of 6 years primarily in databases (T-SQL and Performance Tuning). I have worked on different versions starting with SQL Server 2000 to SQL Server 2008 R2 for companies like Accenture and Airtel. I have completed Microsoft certification in MCTS (SQL SERVER). I did my MCA (Masters Of Computer Application) degree from Bangalore and have a BCA (Bachelor Of Computer Applications) from Indore.


Leave a comment on the original post [rahulsahay123.blogspot.com, opens in a new window]

Loading comments...