Skip to main content

How to keep history of SQL Server stored procedure revisions


28down voteaccepted
While I am in full agreement that source control is the right way to do this, I also understand that not all environments are disciplined enough to rely on that alone (if at all), and that sometimes changes have to made directly to keep the app running, save a client, what have you.
You can use a DDL trigger to keep all revisions in a table in a separate database (and of course back up that database frequently). Assuming you have a utility database:
USE Utility;
GO


CREATE TABLE dbo.ProcedureChanges
(
    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    EventType    NVARCHAR(100),
    EventDDL     NVARCHAR(MAX),
    DatabaseName NVARCHAR(255),
    SchemaName   NVARCHAR(255),
    ObjectName   NVARCHAR(255),
    HostName     NVARCHAR(255),
    IPAddress    VARCHAR(32),
    ProgramName  NVARCHAR(255),
    LoginName    NVARCHAR(255)
);
Now in your database, first let's grab what we'll call "initial control" - the current version of the stored procedures:
USE YourDB;
GO

INSERT Utility.dbo.ProcedureChanges
(
    EventType,
    EventDDL,
    DatabaseName,
    SchemaName,
    ObjectName
)
SELECT
    N'Initial control',
    OBJECT_DEFINITION([object_id]),
    DB_NAME(),
    OBJECT_SCHEMA_NAME([object_id]),
    OBJECT_NAME([object_id])
FROM
    sys.procedures;
Now to capture subsequent changes, add a DDL trigger to the database:
USE YourDB;
GO

CREATE TRIGGER CaptureStoredProcedureChanges
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);

    SELECT @ip = client_net_address
        FROM sys.dm_exec_connections
        WHERE session_id = @@SPID;

    INSERT Utility.dbo.ProcedureChanges
    (
        EventType,
        EventDDL,
        SchemaName,
        ObjectName,
        DatabaseName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();
END
GO
Over time it will become easy to see and compare changes to procedures, watch new procedures get added to the system, see procedures get dropped, and have a good idea of who to talk to about any of these events.
More information here:



Ref: https://dba.stackexchange.com/questions/33541/how-to-keep-history-of-sql-server-stored-procedure-revisions

Labels:

Comments

Post a Comment

Popular posts from this blog

ASP.NET MVC - Set custom IIdentity or IPrincipal

Here's how I do it. I decided to use IPrincipal instead of IIdentity because it means I don't have to implement both IIdentity and IPrincipal. Create the interface interface ICustomPrincipal : IPrincipal { int UserId { get ; set ; } string FirstName { get ; set ; } string LastName { get ; set ; } } CustomPrincipal public class CustomPrincipal : ICustomPrincipal { public IIdentity Identity { get ; private set ; } public bool IsInRole ( string role ) { return false ; } public CustomPrincipal ( string email ) { this . Identity = new GenericIdentity ( email ); } public int UserId { get ; set ; } public string FirstName { get ; set ; } public string LastName { get ; set ; } } CustomPrincipalSerializeModel - for serializing custom information into userdata field in FormsAuthenticationTicket object. public class CustomPrincipalSerializeMode...
8 comments
Read more

Validate credit card number with Mod 10 algorithm in C#

Introduction All you know what information contains in your NIC number. But do you know what information contains in the Credit Card Number? Following article provides brief details about what information contain in your credit card and demonstrates to how to validate credit card number using mod 10 (Luhn) algorithms with C#. Background  Card Length   Typically, credit card numbers are all numeric and the length of the credit card number is between 12 digits to 19 digits.  14, 15, 16 digits – Diners Club 15 digits – American Express 13, 16 digits – Visa 16 digits - MasterCard   For more information please refer  http://en.wikipedia.org/wiki/Bank_card_number . Hidden information  Major Industry Identifier (MII)   The first digit of the credit card number is the Major Industry Identifier (MII). It designates the category of the entry which issued the card.     1 and 2 – Airlin...
1 comment
Read more

Web Services Description Language Tool (Wsdl.exe)

Ref:  https://msdn.microsoft.com/en-us/library/7h3ystb6(VS.80).aspx The Web Services Description Language tool generates code for XML Web services and XML Web service clients from WSDL contract files, XSD schemas, and .discomap discovery documents. wsdl [options] {URL | path} Argument Description URL The URL to a WSDL contract file (.wsdl), XSD schema file (.xsd), or discovery document (.disco). Note that you cannot specify a URL to a .discomap discovery document. Path The path to a local WSDL contract file (.wsdl), XSD schema file (.xsd), or discovery document (.disco or .discomap). Option Description /appsettingurlkey: key or /urlkey: key Specifies the configuration key to use in order to read the default value for the URL property when generating code. When using the   /parameters   option, this value is the   <appSettingUrlKey>   element and contains a string. /appsettingbaseurl: baseurl or /baseurl:...
Post a Comment
Read more