As a database administrator, I have encountered many occurrences in which a business user has asked to provide the number of rows for tables within a database. If you haven’t been asked yet, I’m sure the time will come. When it does, I have a script that you can add to your toolbox that will allow you to fulfill the request!

This script returns the schema name, table name, whether the table is a heap or has a clustered index, and row count for every table within a database.

SELECT sch.NAME AS 'Schema'
,tab.NAME AS 'Table'
,CASE
WHEN par.INDEX_ID = 0
THEN 'Heap'
WHEN par.INDEX_ID = 1
THEN 'Clustered Index'
END AS 'Index Type'
,SUM(par.rows) AS 'Rows'
FROM sys.tables tab
INNER JOIN sys.partitions par ON tab.OBJECT_ID = par.OBJECT_ID
INNER JOIN sys.schemas sch ON tab.SCHEMA_ID = sch.SCHEMA_ID
WHERE par.INDEX_ID < 2
GROUP BY sch.NAME
,tab.NAME
,par.INDEX_ID
ORDER BY SUM(par.rows) DESC

Below is the result set returned when the script is run against AdventureWorks2012.

Not only can you quickly capture table row counts with this script, you can also identify heaps (tables without clustered indexes).