I no longer work at Microsoft, so please don't bother leaving a comment here or trying to contact me through my MSDN blog.
You can find my new blog at http://www.technologytoolbox.com/blog/jjameson. My new site also provides copies of all posts from my MSDN blog.
Here is yet another of the SQL scripts that I like to keep handy in my toolbox: Count Records in All Tables.sql
Sometimes when I get "dropped into" a consulting situation with a new customer, I need to quickly get acquainted with one or more of their SQL Server databases. One of the first things I usually like to know is: "What are the largest tables in the database in terms of the number of rows?"
While you could certainly craft some SQL to SELECT COUNT(*) from each user table, this is very inefficient. A much better way is to simply query the system tables as shown below:
SELECT COUNT(*)
SELECT sysobjects.Name , sysindexes.Rows FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id WHERE type = 'U' AND sysindexes.IndId < 2 ORDER BY sysobjects.Namecode