Post reply

ALTER PROC...

  • July 20, 2005 at 11:26 pm

    #66776

    Hi Everybody,

    Can anybody tell me that how to know when the Stored Procedure OR Table is altered ?

    When I create a Stored Procedure using CREATE PROC, I can query the sysobjects table to know when it was created (crdate) , but what happens when I use ALTER PROC, how do I know when it was changed ?

    Is there anyway to know when it was changed ?

    Thanks in Advance

     

    Live Life Like Lion !!!

  • July 21, 2005 at 12:32 am

    #575983

    SQL doesn't track of changes made when you use ALTER PROC. All that happens is the text of the stored procedure is replaced in syscomments.

    To find out when it changed you'd have to do something like comparing against previous backups.

     

    --------------------
    Colt 45 - the original point and click interface

  • July 21, 2005 at 7:03 am

    #576050

    I have a "comments" section in every stored procedure where I note the date that any changes were made - I also store my procedures in source safe - & as phill says - do a "diff" between the 2 versions to see what has changed!







    **ASCII stupid question, get a stupid ANSI !!!**

  • July 21, 2005 at 9:20 am

    #576120

    Thanks Phils and Sushila for your time.

    I know the Versioning systems VSS and CVS and I am doing the same now.

    But is there any way to find out ? - just to know.

     

    Live Life Like Lion !!!

  • July 21, 2005 at 10:01 am

    #576139

    Like previously stated, No.

    I guess you could create a trigger on syscomments and record the offender and time in a different table. But I'm not sure.

    sushila got anything on this?

  • July 21, 2005 at 10:12 am

    #576144

    Triggers on system tables aren't supported and aren't guaranteed to fire. Tracing through Profiler or the SQL Server trace sprocs would allow you to keep track of changes, though you'll have tweak 'em pretty good just to track object changes. Also, you'd need to have a stored procedure run at start up that took care of this if you went the trace sprocs route.

    K. Brian Kelley
    @kbriankelley

  • July 21, 2005 at 10:12 am

    #576143

    >>I guess you could create a trigger on syscomments and  ... <<

    Triggers on system tables are not ... Ermm ... supported

     


    * Noel

  • July 22, 2005 at 2:12 am

    #576319

    Get some database change management software. That way you can work with your source control in the way it was intended. Application code is usually held in source control why not database code? Probably because it too hard to see what changes have been made and extract those changes and promote them to a database. DB Ghost does this hard work for you giving you the kind of control over SQL source code that developers have been used to for all their procedural code. Everything else that I've ever seen is ultimately just a fudge which is why I created the DB Ghost set of components for database change management. You can get an evaluation copy of all the components from http://www.innovartis.co.uk/database_change_management_evaluation.aspx

    and you may want to read an article on the subject: http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf

     

     

    regards,

    Mark Baekdal

    http://www.dbghost.com

    http://www.innovartis.co.uk

    +44 (0)208 241 1762

    Build, Comparison and Synchronization from Source Control = Database change management for SQL Server

     

     

     

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

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