How to get all table's total row count?

Deepak M

I have a large database having a total table count of more than 100, and in some tables, the total records count is more than 20 Cr. So I use Count() for each table it takes so much time to get the total record count of all tables. Can anyone help me with how can I get all the records count of all tables in less time?

Thank you.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty4y ago
Accepted answer
Hey, by using exec sp_spaceused 'tablename' you can get total rows count very quickly.
if you want all table with total count then use exec sp_msforeachtable 'exec sp_spaceused [?]'
You can insert the above query result in a table like below query.
DECLARE @tables TABLE(tablename NVARCHAR(150), totalrows INT, reserved NVARCHAR(20),
data NVARCHAR(20), indexsize NVARCHAR(20), unused NVARCHAR(20))
INSERT INTO @tables EXEC sp_msforeachtable 'exec sp_spaceused [?]'
SELECT tablename, totalrows FROM @tables ORDER BY tablename
Sachin Singh4y ago
Test this
SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'YourTableName' AND (index_id < 2)