Counting Rows in All Database Tables in SQL Server
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