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