In this post, I provide one SQL script to find the total row count of tables in a Database of SQL Server.
Just imagine that your table has more than 10000000 rows and you require to get row count of this table, SELECT COUNT(1) is not always the best solution to get row count.
Now one more case, If you require to compare all row count of tables between two servers and you have the total number of tables is 500.
Is it the best way to write individual COUNT query for each table ?
No: Because all tables have one default primary partition and you can find this partition information in sys.partitions table.
QUOTENAME(SCHEMA_NAME(SO.schema_id)) + '.' + QUOTENAME(SO.name) AS TableName
, SUM(SP.Rows) AS TotalRowCount
FROM sys.objects AS SO
INNER JOIN sys.partitions AS SP
ON SO.object_id = SP.object_id
WHERE SO.type = 'U'
AND SO.is_ms_shipped = 0x0
AND index_id < 2
ORDER BY 1
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.