What are the system tables / fields that hold record counts

[PTCruiserII]

I would like to know what system table(s) and fields (or better yet the SQL statement itself) in those tables that contain the record counts for all tables in the current database.

Probably something I should know...

Thanks for your help

 

[nbrk]

Try this, remember this is a sproc, so create this first

create procedure tabcount @tablename vharchar(64)
AS
select a.name, rowcnt(b.doampg)
from sysobjects a, sysindexes b
where a.id = b.id
and a.name like @tablename




after that you can really use this to your adavntage, by executing

exec tabcount '%'
go

this will give you the row count of every table

or you could do

exec tabcount 'cust%'
go
which would give you the row count for every table starting with the word cust

Again I hope my memory is not failing me,


Good Luck

 

[nbrk]

obviously vharchar is varchar......

 

[JMCraig]

Just a caveat, this gives you an approximate count (calculated based on how many rows fit on a table). In practice, this may be off by a few rows. And, at least on older Sybase versions, having your statistics up to date improved the accuracy. Finally, you do need to be sure you limit it as shown in bold too (otherwise you'll get a bunch of nulls back for any non-clustered indexes on a table--indid 1 is the clustered index; indid 0 is info on the table itself when there's no clustered index--you don't have both on one table, but you can have several indexes with indid > 1 ):

create procedure tabcount @tablename varchar(64)
AS
select a.name, rowcnt(b.doampg)
from sysobjects a, sysindexes b
where a.id = b.id
and b.indid < 2
and a.name like @tablename

BOL J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[PTCruiserII]

HI JMCraig

I wanted to run this from an ODBC connection - not from a stored procedure.

Is the rowcnt(b.doampg)a function only available to stored procedures and if so, what would I translate this into to get it from ODBC