May 31, 2008 at 8:36 am
In wandering around the internet I thought that I had found a query that would find recently updated/altered stored procs and tables. But for the life of me I cannt find it again or remember the search terms that I used to find it.
So my questions is, does any one know what this query is? Or if it's even possible. After all I may have just drempt such a thing. I commonly make a change to my development DB then dont work on it agian for a week or two and forget what I changed.
Thanks for any help anyone can give.
Tim
May 31, 2008 at 9:02 am
In 2005, there are two things that come to mind (well - two different method to get to the same place):
- there's a default trace running, which keeps track of schema changes
- assuming SP2 or later, the "standard reports" give you the ability to get to a "Schema change report". Right-click on the SERVER name to find it.
They're both pulling from that default trace, so essentially the same info, just one is repackaged as a report.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 31, 2008 at 10:02 am
Hmm well thats not quite what I need, though it will be usefull as well. The only problem with that is it is showing some of my job's and things that I really "didn't change" like a reorg on an index is showing up as an alter.
But that is a good tool that I did not even know was there. I'm still learing SQL Server and all the fun stuff in it.
I'll keep looking and perhaps some one knows the answer still. Because like I said it's just a simple 3 or 4 line query that shows what and when it changed. I'm not worried about who because it's only me doing it.
Thanks agian for the tip thou,
Tim
May 31, 2008 at 7:46 pm
I ended up find it again, and it works jsut as good as it did before.
SELECT [name],
create_date,
modify_date
FROM sys.views
WHERE modify_date > DATEADD(day,-30,GETDATE())
Works on sys.views, sys.procedures, and sys.tables.
Just in case anyone cares
Tim
May 31, 2008 at 10:51 pm
nice that you found your query, but you should really use a source control system.
---------------------------------------
elsasoft.org
November 5, 2008 at 7:12 am
Tim Meers,
Does the code you suggested provide the last time a table was updated?
I went directly into a table in SQL 2005, and change a column. I closed
the table. The 'modify_date' is still showing the previous date.
I'm using the following to look up:
SELECT modify_date
FROM sys.views
Am I using the correct table?
This was also suggested on a previous web site at:
http://weblogs.sqlteam.com/joew/archive/2007/10/11/60367.aspx
November 5, 2008 at 9:20 am
The modified date is not the last time a Table was UPDATEd, but rather the last time that is was ALTERed.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 7, 2008 at 9:44 am
Barry,
...Modified in terms of changing a column description, adding a column, doing some type of work on the Table Definitions itself, NOT the underlying data...is this correct from when you state "ALTER?"
So, just to clarify, an UPDATE, INSERT, or DELETE command on a Table must be issued against the table to affect a getdate(). I'm always in a table modifying it using ASP, so this works as I expect, resolved by your previous suggestions. But there is no where in the underlying system tables which expose "last update" information independent of issuing the UPDATE/INSERT/DELETE commands.
Once again, thanks!
-uman
November 7, 2008 at 10:26 am
umanpowered (11/7/2008)
...Modified in terms of changing a column description, adding a column, doing some type of work on the Table Definitions itself, NOT the underlying data...is this correct from when you state "ALTER?"
Yes.
So, just to clarify, an UPDATE, INSERT, or DELETE command on a Table must be issued against the table to affect a getdate(). I'm always in a table modifying it using ASP, so this works as I expect, resolved by your previous suggestions. But there is no where in the underlying system tables which expose "last update" information independent of issuing the UPDATE/INSERT/DELETE commands.
I am not sure what "affect a getdate()" means here, so this is a little bit confusing.
However, at guess: Yes(?), SQL Server does not save the last time a table had a DML statement (UPDATE, INSERT, DELETE) executed against it. If you want that, you have to write SQL code to do that yourself.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 4, 2008 at 9:14 am
The modified date is not the last time a Table was UPDATEd, but rather the last time that is was ALTERed.
Correct, and thats what I was looking to find. I don't think I would need to know when a table last had an UPDATE/INSERT/DELETE run on it, only ALTER/CREATE TABLE.
Am I using the correct table?
This was also suggested on a previous web site at:
http://weblogs.sqlteam.com/joew/archive/2007/10/11/60367.aspx%5B/quote%5D
I guess I should have posted a link too but was busy getting to work on things once I found the script and didn't think to do it.
nice that you found your query, but you should really use a source control system.
I'm now working on implementing Visual Sourse Safe for my project. I have to get the database in there still though.
Thanks for the help every one. Sorry this reply is some what late.
Tim
December 4, 2008 at 9:00 pm
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 5, 2008 at 1:26 am
umanpowered (11/5/2008)
I went directly into a table in SQL 2005, and change a column. I closedthe table. The 'modify_date' is still showing the previous date.
I'm using the following to look up:
SELECT modify_dateFROM sys.views
Am I using the correct table?
In addition, you're querying sys.views. That contains only views, not tables. The modify date in there will be the date someone last ran ALTER VIEW ...
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
December 5, 2008 at 8:37 am
I've had false positives on that column. I haven't investigated why, but I've found stored procs that had no changes, but where the "last modified" in the system view said they had been. (I have a DDL trigger that tracks actual changes, and source control, and both of those have shown that the definition of the proc in question had not changed, but the date showed it had.) I also checked the traces, and no record of a change there.
I don't know if something may have happened to cause that date to update, but all evidence said nothing at all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply