This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(108.80000000000001, 96%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EU%3C/text%3E%3C/svg%3E)
Hi expert,
we've already know how to get last modified data on specific table with SQL Trigger but how do we getting last modified Database either modified data(DML) or modified table\view (DDL) inside?
does anyone have query for this?
great thanks & appreciation for all answer & advice
Warm regards,
UrBel
The best method would still be to set up audit with SQL Server audit. But it would still a bit messy.
What are you are really looking for? The request sounds a little odd to me, but maybe I am misunderstanding something.
Hi Erland,
I'm looking for Last modified Database information either table data changes or objects structure(SP,View,schema etc.) inside the database...
if use Audit SQL it should wait to running several next day, I just need immediately the information when it required
We have almost hundreds DB at each instances on several VM DB servers..
Thank you
UrBel
if use Audit SQL it should wait to running several next day, I just need immediately the information when it required
You can't get the information before you start collecting it. SQL Server does not track this automatically.
We have almost hundreds DB at each instances on several VM DB servers..
And what has this to do with it? Can you explain what you really want to achieve? As I said, your request appears a little odd.
I'm looking for Last modified Database information either table data changes or objects structure(SP,View,schema etc.) inside the database...
You get the latest modify date/time of objects with
select max(obj.modify_date) AS last_modify_date
from sys.objects as obj
Thanks Olaf,
It has informed last modified DDL structure on objects of DB, just need add the information last modified table data changes (DML) but it apparently should be created by SQL Trigger..
regards,
UrBel
Beware that modify_date is also set on index rebuilds. Did that actually modify the table? Physically it did. Logically, it did not.
This is why I say that auditing is the only option. This also gives you the choice to decide what counts as a modification.