By using sys.tables and sys.partitions we can find a list of database tables with number records(rows) in a table.
SELECT DISTINCT t.NAME AS TableName,
p.rows AS RowCounts
FROM sys.tables t
INNER JOIN sys.partitions p ON t.object_id = p.OBJECT_ID
ORDER BY t.Name
Query :
SELECT DISTINCT t.NAME AS TableName,
p.rows AS RowCounts
FROM sys.tables t
INNER JOIN sys.partitions p ON t.object_id = p.OBJECT_ID
ORDER BY t.Name