How to get list of Tables and count of rows from table

Question

Hi,

How to get list of Tables and count of rows from table by writing single Query.

Expected O/P as below

Answer 1

Below is an example using the object catalog views:

SELECT s.name AS SchemaName, t.name AS TableName, SUM(p.rows) AS TableRowCount
FROM sys.schemas AS s
JOIN sys.tables AS t ON t.schema_id = s.schema_id
JOIN sys.partitions AS p ON p.object_id = t.object_id
GROUP BY s.name, t.name
ORDER BY SchemaName, TableName;

Answer 2

It will give wrong results in case you have PK and Unique key scenarios.

Add Index_ID as 1, 0 clause; 1 = table with PK, 0 = table without PK. Corrected query:

SELECT s.name AS SchemaName, t.name AS TableName, SUM(p.rows) AS TableRowCount

FROM sys.schemas AS s

JOIN sys.tables AS t ON t.schema_id = s.schema_id

JOIN sys.partitions AS p ON p.object_id = t.object_id

where p.index_id IN (1, 0)

GROUP BY s.name, t.name

ORDER BY 3 desc, SchemaName, TableName;