Number of rows per table

Well first thing first, when you have to analyze a database, you might want to see how big it is, and how much records are there in each table.

One way is to get count for each table like

Select Count(0) From Table1;

But if you have more then 100 tables in the system it will be very difficult to do so, so you might need a query which can list all the tables in the database with its row count, thanks to the master database which store all these info for you. Here is the query which will bring the list for you.


Select 
sob.name,
dbps.row_count
From sys.dm_db_partition_stats dbps
Inner join sys.sysobjects sob on sob.id = dbps.object_id
Where sob.type = 'U' And dbps.index_id = 1

About this Blog

My Manager just assigned me a task to optimize our databases, so I decided that while I am doing any optimization to the database I will write a blog about each step, so it will be helpful to general public and our team if we were able to make any breakthrough.

 Keep fruit out where you can see it. That way you’ll be more likely to eat it. Keep it out on the counter or in the front of the fridge.