Here is the script to get the list of tables with number or rows in each table [SQL Server 2008]. This will help you when you want to know which tables are heavy. When it comes to performance issue this query is very helpful. Recently I find this query from here thanks to Jeremy Jameson. I added this query in this post as i want to make it handy.
SELECT sysobjects.Name , sysindexes.Rows FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id WHERE type = 'U' AND sysindexes.IndId < 2 ORDER BY
2 desc
It will produce the result as shown below.
Table1 50000
Table2 45000
Table3 40000
Table4 10000
