user20325 user20325 - 5 months ago 6
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
SELECT COUNT_BIG(*) AS NumberOfRows,
''?'' AS TableName FROM ?'

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

DROP TABLE #RowCounts


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

and
''?'' 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

try using sys.dm_db_partition_stats DMV..

  select 
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..

CREATE TABLE #counts
(
    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

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

Comments