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

Scripts to list all user tables

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