How many rows does your largest SQL Server table contain?

Often database administrators and users want to know how many rows are there in each table or how big is the largest table in the database. Run the following SQL batch from your SQL query window to generates a list of all tables in your database together with the number of rows in each one. The list is sorted by table name but you can easily change the ORDER BY clause to sort it by the number of rows:

Syntax

USE db1
SELECT o.name as [Name], i.rowcnt as [Row Count]
FROM sysindexes i
INNER JOIN sysobjects o ON i.id = o.id
WHERE indid < 2 AND o.xtype = 'U'
ORDER BY Name

This query joins system tables to retrieve information about user tables (where xtype=’U’) and their row counts. You can modify the ORDER BY clause to sort by row count to quickly see your largest tables.