Sql-server – How to keep history of SQL Server stored procedure revisions

sql serverstored-procedures

Note: I am not asking about full version control.

Is there any way automatically to keep a history of stored procedures on SQL Server.

Similar to how Google Docs automatically keeps a history of versions of documents and Wikipedia automatically keeps a history of versions of articles.

I don't want users updating stored procedures to have also to maintain a repository of stored procedures. This is too much work and people won't do it.

Hopefully this is something I can turn on in SQL Server…

(And by stored procedures really I mean functions, triggers, etc. Basically everything under Programmability.)

I posted to https://stackoverflow.com/questions/14522224/how-to-keep-history-of-sql-server-stored-procedure-revisions first cos I suspect it would get more views there.

Best Answer

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:

http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

Related Question