Fastest way to get ROWCOUNT of any table..

During performance health check, we generally use below query to get total number of rows in any table.

SET STATISTICS IO ON 

SELECT COUNT(*) FROM CustomerTbl WITH(NOLOCK)

But this can be take lot of time to scan whole table if table is having TB's of data.

So, below is the alternative query to get row count of all the tables of your database in one go, and if you want, you can search for a particular table as well.

SELECT  SCHEMA_NAME(t.schema_id) SchemaName,

        t.[name] TableName, 

        SUM(p.row_count) TotalRows

FROM sys.tables t

INNER JOIN sys.dm_db_partition_stats p

ON t.object_id = p.object_id AND t.type_desc = 'USER_TABLE'  AND p.index_id IN (0,1)

-- WHERE t.[name] = 'CustomerTbl'

GROUP BY t.schema_id, t.[name]

ORDER BY TotalRows DESC