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]

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" <ri**@abasoftwa re.com> wrote in message
news:28******** *************** ***@posting.goo gle.com...

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

[Shervin Shapourian]

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" <ch***@hrn.or g> wrote in message news:bk******** **@reader2.nmix .net...

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" <ri**@abasoftwa re.com> wrote in message
news:28******** *************** ***@posting.goo gle.com...

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

[Erland Sommarskog]

Rick (ri**@abasoftwa re.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_MSforeachtab le:

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

Note that this procedure is undocumetned.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Rick Navaro]

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!