getting table counts

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,
RickNAssuming your statistics are up to date you can use

SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID('<table_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@.abasoftware.com> wrote in message
news:28d7cbb9.0309231030.13e8f503@.posting.google.c om...
> 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|||So this would be the non-cursor solution:

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

Shervin

"CJ" <chris@.hrn.org> wrote in message news:bkqdqr$34q$1@.reader2.nmix.net...
> Assuming your statistics are up to date you can use
> SELECT rows
> FROM sysindexes
> WHERE id = OBJECT_ID('<table_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@.abasoftware.com> wrote in message
> news:28d7cbb9.0309231030.13e8f503@.posting.google.c om...
> > 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|||Rick (rick@.abasoftware.com) writes:
> 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.

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_MSforeachtable:

EXEC sp_MSforeachtable '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/techin.../2000/books.asp|||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.name)
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!