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:
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id
type = 'U'
AND sysindexes.IndId < 2