SQL query that lists the entire database by date modified

apollo7
apollo7 used Ask the Experts™
on
Does anyone have a SQL query that queries the entire database and lists the records by date modified?  I am looking for a count of how many records in each table was modified in 2018, 2017 and so on back to 2008.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooter

Commented:
Are you using Change Tracking, Change Data Capture or Versioning on your database?
Fractional CTO
Distinguished Expert 2018
Commented:
Provide a schema for your table + likely someone can assist.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
Does every table have a "date modified" (or similar name) column?
or
Are you really just wanting this analysis on those tables which do have such a column?

Is there more than one column name involved? What is/are the column name/s?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooter
Commented:
Without some sort of change tracking/versioning, I'm not certain how to accomplish what you propose.  (I suppose it might be theoretically possible with transaction logs... maybe... but that won't be a SQL query to get the results.)

If you're just trying to get an inventory of when rows in the existing database were last changed.  So, in Aug 2018, you want to count rows last modified in 2017, for example, I believe you'd want to have enabled Change Tracking.  That won't capture that the row was changed, for example, six times in 2017 or that it was modified 28 times in the years before that.

If you're trying to track all the changes, you'd have wanted to setup Change Data Capture.

Or, you may have something custom in your schema which might help with collecting the metadata you're seeking?  As David says above... your table schema would be useful to finding a solution to help you.

Author

Commented:
Thank everyone for the information, I have gone through about 100 tables and did a count based Modified On date.  This field is not available in every table, so I had to manually select the tables to review.

The information you all provided was enlightening so I know how this needs to be setup for future use.

Author

Commented:
I have split the points to everyone that responded to my question, thanks for the assistance..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial