Post reply

How to retrieve change date from stored procedures

  • February 19, 2004 at 2:24 am

    #163017

    Hi,

    I need to generate daily scripts for altered or new stored procedures. Is there a way to automaticaly know wich ones were altered or created in any given date?

    Thanks,

    RR

  • February 19, 2004 at 8:49 pm

    #494851

    crdate and schema_ver in sysobjects table may help you.

  • February 20, 2004 at 1:08 am

    #494869

    crdate gets set when you create or drop/create the procedure.  unfortunately it doesn't get touched when you alter the procedure.  as wz700 points out, schema_ver gets updated on alter... but it's only incremented... nothing to indicate when it changed.

    To use schema_ver you'll need to daily save stored proc info (suggest name, id and schema_ver) and compare it the next day.  If you're paranoid that a clever hacker may reset schema_ver, an additional suggestion is maybe also saving a binary_checksum of the procedure's text (from syscomments) as another comparison that can be made.


    Cheers,
    - Mark

  • February 23, 2004 at 2:11 am

    #495104

    Is there a specific reason for this?

    Just asking, because if it is for some kind of security audit, you might want to take a look at C2 auditing in BOL, although I guess it will be an overkill.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 23, 2004 at 7:33 am

    #495129

    The only reason is becaus im a project leader in a development environment and i need to track changes to the database in order to create update scripts for tha QA team.

    According to the replies i've received i managed to develop a VB6 application to track version changes using schema_ver to compare.

    Its working fine and solved my real problem.

    Thanks for all the help that you gave me.

    RR

Viewing 5 posts - 1 through 4 (of 4 total)

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