SQL query that lists the entire database by date modified

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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Are you using Change Tracking, Change Data Capture or Versioning on your database?
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
Provide a schema for your table + likely someone can assist.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Does every table have a "date modified" (or similar name) column?
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?
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
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.
apollo7Author 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.
apollo7Author Commented:
I have split the points to everyone that responded to my question, thanks for the assistance..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.