A Simple Way to Get Row Counts from Multiple tables

Question

Post reply

A Simple Way to Get Row Counts from Multiple tables

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
November 4, 2010 at 3:47 pm

#228605

Hello Everyone
Is there an easy way to get the row counts from multiple tables in a database?
I am executing a query that selects the table name from the sys.tables table, that uses a where clause to select only some of the tables that I want.
So, how can I select the table name, along with the row count from each table?
Thanks
Andrew SQLDBA

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Perhaps something like this?

select

o.name,

i.rowcnt

from

sysobjects o

JOINsysindexes I

ONo.id = i.id

where

o.type = 'u'

and i.indid IN (0,1)

order by

o.name

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

Very nice, that is just what I was after.

Been one of those long days, my brain is about to drip out of my ears

Thanks alot

Andrew SQLDBA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

A better way, direct from the QotD for Oct 19, 2010:

select

sum(row_count)

from

sys.dm_db_partition_stats

where

(index_id = 0 or index_id = 1);

sysobjects and sysindexes are depreciated and could go away in the next release.

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 4

Lynn Pettis (11/4/2010)
A better way, direct from the QotD for Oct 19, 2010:
select
sum(row_count)
from
sys.dm_db_partition_stats
where
(index_id = 0 or index_id = 1);
sysobjects and sysindexes are depreciated and could go away in the next release.

Modified a little bit:

select

so.[name], sum(row_count)

from

sys.dm_db_partition_stats dp

inner join sys.objects so

on dp.[object_id] = so.[object_id]

where

(dp.[index_id] = 0 or dp.[index_id] = 1)

and so.[type] = 'U'

group by so.[name]

order by so.[name];

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 5

Ripped from:

http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

create table #rowcount (tablename varchar(128), rowcnt int)

exec sp_MSforeachtable

@command1 = 'insert into #rowcount select ''?'',

count(*) from ?'

--,@whereand = 'and name like ''p%'''

select top 5 * from #rowcount

order by tablename

drop table #rowcount

A Simple Way to Get Row Counts from Multiple tables

Cookies on SQLServerCentral