user20325 user20325 - 1 year ago 60
SQL Question

Get row count for a list of tables

In SQL 2008, how can I get a row count for a list of tables?

I have a database where I would like to get the row count for all tables that begin with 'BB'

I've tried multiple variations of this:

CREATE TABLE #RowCounts(NumberOfRows BIGINT,TableName VARCHAR(128))

EXEC sp_MSforeachtable 'INSERT INTO #RowCounts
''?'' AS TableName FROM ?'

SELECT TableName,NumberOfRows
FROM #RowCounts
ORDER BY NumberOfRows DESC,TableName


...Throwing in stuff like
''?'' AS TableName FROM ? WHERE ? LIKE 'BB%'

''?'' AS TableName FROM ? WHERE ''?'' LIKE 'BB%'

I'm sure that there has to be a way to do this. If you can get the rowcount for all tables, you should be able to get it for some tables...right?

Thanks much!

Answer Source

try using sys.dm_db_partition_stats DMV..

object_name(object_id) as tablename,sum(row_count) as totalrows
 from sys.dm_db_partition_stats
 where object_name(object_id) like 'Bb%'--gives tables count which start with bb*
 group by object_id 

This may not be accurate enough (very little deviation) when you have lot of inserts ,deletes and check the count immediately..

if you are bent on using sp_msforeach..

    table_name varchar(255),
    row_count int

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts  where table_name like 'BB%' ORDER BY table_name, row_count DESC

How to fetch the row count for all tables in a SQL SERVER database

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download