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 [rows] desc