getting table counts

[Rick]

I want to get a resultset of every table in the database, with the
current record count of each. What is the easiest way to do this?

I can get the list of tables with:

Select s.name from sysobjects s where xtype = 'U'

each s.name is a table name, but I'm not sure how to join a record count
column to the resultset.

Thanks,
RickN

[CJ]

Re: getting table counts

Assuming your statistics are up to date you can use

SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID('<tab le_name>') AND indid < 2

This will perform better than

SELECT COUNT(*) from <table_name>

This info is from http://www.sql-server-performance.com/

You could use a cursor to loop through the list of tables and stuff the
counts into a temp table. Perhaps someone else will have a way to do this
without a cursor.

Hope this helps,

CJ



"Rick" <rick@abasoftwa re.com> wrote in message
news:28d7cbb9.0 309231030.13e8f 503@posting.goo gle.com...[color=blue]
> I want to get a resultset of every table in the database, with the
> current record count of each. What is the easiest way to do this?
>
> I can get the list of tables with:
>
> Select s.name from sysobjects s where xtype = 'U'
>
> each s.name is a table name, but I'm not sure how to join a record count
> column to the resultset.
>
> Thanks,
> RickN[/color]

[Shervin Shapourian]

Re: getting table counts

So this would be the non-cursor solution:

select o.name, i.rows
from sysobjects o, sysindexes i
where i.id = OBJECT_ID(o.nam e)
and i.indid = 0

Shervin

"CJ" <chris@hrn.or g> wrote in message news:bkqdqr$34q $1@reader2.nmix .net...[color=blue]
> Assuming your statistics are up to date you can use
>
> SELECT rows
> FROM sysindexes
> WHERE id = OBJECT_ID('<tab le_name>') AND indid < 2
>
> This will perform better than
>
> SELECT COUNT(*) from <table_name>
>
> This info is from http://www.sql-server-performance.com/
>
> You could use a cursor to loop through the list of tables and stuff the
> counts into a temp table. Perhaps someone else will have a way to do this
> without a cursor.
>
> Hope this helps,
>
> CJ
>
>
>
> "Rick" <rick@abasoftwa re.com> wrote in message
> news:28d7cbb9.0 309231030.13e8f 503@posting.goo gle.com...[color=green]
> > I want to get a resultset of every table in the database, with the
> > current record count of each. What is the easiest way to do this?
> >
> > I can get the list of tables with:
> >
> > Select s.name from sysobjects s where xtype = 'U'
> >
> > each s.name is a table name, but I'm not sure how to join a record count
> > column to the resultset.
> >
> > Thanks,
> > RickN[/color]
>
>[/color]

[Erland Sommarskog]

Re: getting table counts

Rick (rick@abasoftwa re.com) writes:[color=blue]
> I want to get a resultset of every table in the database, with the
> current record count of each. What is the easiest way to do this?
>
> I can get the list of tables with:
>
> Select s.name from sysobjects s where xtype = 'U'
>
> each s.name is a table name, but I'm not sure how to join a record count
> column to the resultset.[/color]

SELECT 'SELECT ''name'', COUNT(*) FROM ' + name
FROM sysobjects
WHERE xtype = 'U'
AND objectproperty( id, 'IsMSShipped') = 1
ORDER BY name

Cut and paste.

If you want to run it unattended, you can use the stored procedure
sp_MSforeachtab le:

EXEC sp_MSforeachtab le 'SELECT ''?'', COUNT(*) FROM ?'

Note that this procedure is undocumetned.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Rick Navaro]

Re: getting table counts


Thanks everyone for the good ideas.
I've implemented the following and it gets me exactly what I need.

select o.name, i.rows
from sysobjects o, sysindexes i
where i.id = OBJECT_ID(o.nam e)
and i.indid < 2 and o.xtype = 'u'

RickN


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!