I was tasked to collect row count information in a system with approx. 500+ tables. They want a way to easily identify which top 20 tables insert the most rows during any period of time. The rowcount of all the tables are populated every hour to a table with tblname, rowcount and datetime stamp.
Any idea how this can be done ?
to be efficient, each of the 500 tables would need an index on their timestamp columns; are those TIMESTAMP/ROWVERSION datatypes, or are they actually DateTime datatypes?
if they are ROWVERSION, then you need a history table, that keeps track of the highest rowversion value at a given hour, per table
SELECT getdate(),'MyTable',MAX(RowVersionColumns) As Val FROM MyTable
that needs to be stuffed into a history table, so that later you can do something like this:
SELECT getdate(),'MyTable',MAX(RowVersionColumn) As Val ,COUNT(*) As TheCount FROM MyTable WHERE RowVersionColumns > (SELECT Val FROM MyHistoryTable WHERE TableName = 'MyTable')
if the column datatype is datetime, then it's a little simpler; but it's still repeated for 500 tables
SELECT getdate(),'MyTable',COUNT(LastUpdatedColumn) As Val FROM MyTable WHERE LastUpdatedColumn BETWEEN DATEADD(hour, -1,getdate()) AND getdate()
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!