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 ?'
ORDER BY NumberOfRows DESC,TableName
DROP TABLE #RowCounts
''?'' AS TableName FROM ? WHERE ? LIKE 'BB%'
''?'' AS TableName FROM ? WHERE ''?'' LIKE 'BB%'
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