Get the number of rows in tables

In order to get the number of rows in tables you can simply run count(*).

But:

1. You need to run it for each table in the database.

2. Fot big tables - it takes time.

So. use thos query in order to get the number of rows in tables, or in one table - set WHERE clause on the table name or ID.

; WITH rCnt AS

(

       SELECT p.object_id, SUM(p.rows) as RowsCount

       FROM sys.partitions p

       WHERE p.index_id IN (0,1) -- heap or clustered index

       GROUP BY p.object_id, OBJECT_NAME(p.object_id)

)

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName,

       t.name as TableName,

       rCnt.RowsCount

FROM   rCnt

JOIN   sys.tables t  ON t.object_id = rCnt.object_id