Get row count for a list of tables

Question

In SQL Server 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?

score 2 · Accepted Answer · edited May 23 '17 at 10:30

2

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

edited May 23 '17 at 10:30

Community

1
1

answered Jun 07 '16 at 19:08

TheGameiswar

27,855
8
56
94

But this still gets the count for all tables. What I'm looking for is the count for all tables where the name starts with "BB". I'm not dead set on using sp_MSForeachtable. I'll be happy with whatever works. The snippit above is just what I've been able to find that gets me close. – user20325 Jun 07 '16 at 19:29
use where clause ( object_name(object_id)) and limit it by using like condition – TheGameiswar Jun 07 '16 at 19:30
I'm sorry to be a pain, but how do I do that? I've been trying but the light won't come on...maybe a short somewhere in my head. :\ – user20325 Jun 07 '16 at 19:38
Okay...Got it! From your first example I changed "from sys.dm_db_partition_stats" to "from sys.dm_db_partition_stats WHERE object_name(object_id) LIKE 'BB%'" THANK YOU!!!! – user20325 Jun 07 '16 at 19:42

score 0 · Answer 2 · answered Jun 07 '16 at 20:19

If database contains heap or clusterd index tables then I would use one of following approaches:

1) sys.partitions.rows

SELECT s.name as schema_name, t.name as table_name, SUM(p.rows) AS SumOfRows
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.partitions p ON p.object_id = t.object_id
WHERE s.name = N'dbo' AND t.name LIKE N'BB%'
GROUP BY s.name, t.name

But rows column isn't accurate (according to MSDN).

2) Or, if I would like accurate numbers then I would use COUNT(*) thus

DECLARE @SqlStatement NVARCHAR(MAX) = N''

SELECT @SqlStatement = 
    @SqlStatement 
    + N' UNION ALL SELECT '
    + '''' + full_name + ''''
    + N' AS full_name, COUNT(*) AS row_count FROM ' + full_name
FROM (
    SELECT QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) AS full_name
    FROM sys.schemas s
    JOIN sys.tables t ON s.schema_id = t.schema_id
    WHERE s.name = N'dbo' AND t.name LIKE N'BB%'
) s

SET @SqlStatement = STUFF(@SqlStatement, 1, 10, N'')    

EXEC sp_executesql @SqlStatement

Get row count for a list of tables

2 Answers2