SQL Server Performance

"Last modified" for sp's

Discussion in 'General DBA Questions' started by frettmaestro, Dec 2, 2003.

  1. frettmaestro New Member

    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"
  2. Luis Martin Moderator

    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
  3. FrankKalis Moderator

  4. satya Moderator

    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
  5. frettmaestro New Member

    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"
  6. satya Moderator

    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
  7. frettmaestro New Member

    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"
  8. satya Moderator

    To be precise, Yes.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  9. Argyle New Member

    Would it be possible to create a trigger that fired when a SP was changed or updated maybe?

    /Argyle
  10. gaurav_bindlish New Member

    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
  11. frettmaestro New Member

    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"
  12. ChrisFretwell New Member

    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
  13. satya Moderator

    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
  14. frettmaestro New Member

    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"

Share This Page