Query to show Database Objects last change

Question

Query to show Database Objects last change

dwilliscp

SSCrazy Eights

Points: 9270
More actions
March 17, 2015 at 8:33 am

#295965

If I skip those objects that were deleted, is there a query that I can run off Master/MSDB that will show all database objects and when they were last modified, or create date if it has not been modified?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No, SQL doesn't track object change history.

You can look at sys.objects in each database and look at the create_date and modify_date, but I think there are things you can do to an object that changes the modify date without actually changing the object.

Sounds like you may want a server-scoped DDL trigger with an auditing table

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 2

GilaMonster (3/17/2015)
No, SQL doesn't track object change history.
You can look at sys.objects in each database and look at the create_date and modify_date, but I think there are things you can do to an object that changes the modify date without actually changing the object.
Sounds like you may want a server-scoped DDL trigger with an auditing table

Gail, I have never created a server DDL trigger... did not even know you could. I will search the web, do you happen to have any good links?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Books Online (the SQL help file) 🙂

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 4

dwilliscp

SSCrazy Eights

Points: 9270

March 17, 2015 at 10:47 am

#1784550

Thanks for your help.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

dwilliscp (3/17/2015)
If I skip those objects that were deleted, is there a query that I can run off Master/MSDB that will show all database objects and when they were last modified, or create date if it has not been modified?

The sys.objects table has always had a "Create_Date" column and, as of 2005, has a "Modify_Date" column. There is nothing, however, that will tell you what the modification actually was. You can glean some of what the modification was because PK's, unique and check constraints, foreign keys, and a couple of other things are also tracked, but not much else.

You would have to do this in the sys.objects table in each database.

{Edit} Crud. Not enough coffee, yet. Gail posted a nearly identical reply. Apologies for the duplication.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 6

Not sure if this is something you're looking for but I recently posted a process on SSC to track these types of things. Feel free to check it out - not certain if it's exactly what you're looking for but it's along the lines of what Gail suggested:

http://www.sqlservercentral.com/articles/trace+files/101777/

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 7

MyDoggieJessie (3/17/2015)
Not sure if this is something you're looking for but I recently posted a process on SSC to track these types of things. Feel free to check it out - not certain if it's exactly what you're looking for but it's along the lines of what Gail suggested:
http://www.sqlservercentral.com/articles/trace+files/101777/

Thanks I will check it out.