SQL Server Performance Forum – Threads Archive
"Last modified" for sp’s
Is it possible to find out when a stored procedure was last updated? I’m struggeling again and again to deploy my sp’s from test to production after large updates because I have to "remember" which ones I changed…and as my signature says I’m not much good at documenting what I do <img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ /><br /><br />–<br />Frettmaestro<br />"Real programmers don’t document, if it was hard to write it should be hard to understand"
There is a lot of third part product to keep tracking about changes in Database. See ours sponsors.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
There is a built-in functionality C2 auditing that lets you monitor this.
However, I think that’s a massive overkill to what you need, so I’ll second Luis. Frank
http://www.insidesql.de
http://www.familienzirkus.de
I’ve used C2 auditing but its an overhead if you’ve persistent blocking with queries, so as suggested by Luis you may rely on Lumigent’s log explorer which is good to read Tlog. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Ok, thanks guys but it is actually the case that I have to use a third-party tool…? These have a tendency to cost quite a bit and I’m on a somewhat tight budget… —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"
If the budget is a factor then you may need to deploy as suggested by Frank as a part of it to run PROFILER all the times. Intermittently SQL doesn’t store the value any where. In this case you can deploy Visual Source Safe to document the schema changes. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Hmm, I was hoping for sometyhing easy…but I guess there’s no such thing. —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"
To be precise, Yes. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Would it be possible to create a trigger that fired when a SP was changed or updated maybe? /Argyle
well actually I had done some research on this and finally came to a road block. You can’t have a trigger on a system table, which in this case happened to be sysobjects. Also last modified date for any object can be found from sysobjects table. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Are you serious? So a simple select of some sort in the sysobject-table would give me what I need? I’ll give it a go… —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"
The refdate in the sysobjects table doesnt necessarily reflect the last change date. I ran the following on one of our production servers (sql7) where I know views and sps had been modified and it returned zero rows. select * from sysobjects (nolock) where refdate <>crdate The crdate is the date the object was originally created. So if all things are dropped and recreated, then this date will be the most recent created date. If an sproc is modified and recompiled from within EM, then this date doesnt change. According to MS, the refdate field is reservered for future use. Chris
It means MS can change that column in future releases, but as for now Chris’s suggestion is valuable to find out. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I did the same tests on my test-database and neither crdate or refdate changed when I did some minor modifications, however I did notice that the fields "basa_schema_ver" and "schema_ver" increased by 16 for every time I ran ALTER PROCEDURE. The problem is that this number never matches my production database because I obviously alter test-procedures *alot* more than production-procedures…hmm… —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"
]]>