Record (row) count by Table in Database

  • Thread starter Thread starter JEB
  • Start date Start date
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
 
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
 
Back
Top