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