Record (row) count by Table in Database

J

JEB

I have a rather critical need to monitor the number of rows for each table in
a database. The Database contains 17 tables, and at any given time any one
of the tables can contain zero (0) rows or several thousand rows.
I was thinking along the lines of creating another table which would
contain: Table Name, Date/Time and the Row Count. and appending the row count
for each table via a summary query. This way, I could see not only the
number of rows in each table at a given point of time but also look at the
'trend' by table.
I have two problems with this approach, the first being the 'table name' and
where to find it. and the second, if the table has zero rows, it will not
append to the table.

Any suggestions?

Thanking you in advance

JEB
 
D

Douglas J. Steele

Try the following query:

SELECT Name, DCount("*", Name) AS NumberOfRows
FROM MSysObjects
WHERE TYPE IN (1, 4, 6)
AND Name NOT LIKE "MSys*"
ORDER BY Name
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top