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.


SELECT 'Owner'=convert(char(10),T.TABLE_SCHEMA),
'Table Name'=convert(char(25),T.TABLE_NAME),
'Record Count'=max(I.rows)
FROM sysindexes I, INFORMATION_SCHEMA.TABLES T
WHERE T.TABLE_NAME = object_name(I.id)
AND T.TABLE_TYPE = 'BASE TABLE'
GROUP BY T.TABLE_SCHEMA, T.TABLE_NAME

Scripts to list all user tables

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