November 4, 2010 at 3:47 pm
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
November 4, 2010 at 3:50 pm
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
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
November 4, 2010 at 4:21 pm
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
November 4, 2010 at 6:31 pm
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.
November 8, 2010 at 12:06 pm
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];
November 9, 2010 at 1:31 pm
Ripped from:
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply