List Record Count for SQL Tables

On way to understand where the data is in one database, is to get record count from all tables in the database. In SQL database, we may get the record count of all tables by using the system tables.

-- Updated page or row count information for the current database
-- DBCC UPDATEUSAGE (0);

SELECT
SCHEMA_NAME(T.SCHEMA_ID) AS SCHEMA_NAME
, T.NAME AS TABLE_NAME 
, I.ROWS
FROM SYS.TABLES T
JOIN SYS.SYSINDEXES I
ON T.OBJECT_ID = I.ID
   AND I.INDID < 2
ORDER BY SCHEMA_NAME, TABLE_NAME

To get record count from a list of selected tables in the Northwind database.


declare @list table
(
 table_name varchar(64)
)

insert into @list values ('Customers')
insert into @list values ('Orders')
insert into @list values ('Products')


SELECT
SCHEMA_NAME(T.SCHEMA_ID) AS SCHEMA_NAME
, T.NAME AS TABLE_NAME 
, I.ROWS
FROM SYS.TABLES T
JOIN SYS.SYSINDEXES I
ON T.OBJECT_ID = I.ID
   AND I.INDID < 2
WHERE T.NAME IN
(
 SELECT table_name FROM @list
)   
ORDER BY SCHEMA_NAME, TABLE_NAME

Using the undocumented sp_MSForEachTable stored procedure

EXEC sp_MSForEachTable 'SELECT ''?'', COUNT(*) FROM ?'

Scripts to list all user tables

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME