22

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.

31

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/

  • 2
    +1 The simplest and native way to do it. My guess is this is the answer the OP was looking for. – Thomas Stringer Jan 25 '13 at 14:03
  • Yep, this will be the go to solution for OP's problem. – Marian Jan 25 '13 at 14:22
  • I love this answer because once you do it then you get automatic versioning at no additional cost. I agree it's not the same as Source Control, but it's a valuable safety net that should not be ignored. – Daniel Williams Nov 12 '15 at 15:16
2

I don't think that there is any way to automatigally keep your SQL Source Code under version control. I mean with Native SQL Server tools. I think you could end up using git or svn, but the best solution for me was to buy Red Gate's Source Control for keeping databases (and stored procedures) under version control.

  • 1
    Sure, a DDL trigger can do this without needing 3rd party tools (see my answer). Of course source control offers much greater control and auditing, and 3rd party tools will have a lot more features than you'll want to write yourself, but they don't have a way to protect you from direct changes - in other words they rely on everyone obeying the source control protocol (which isn't always feasible). – Aaron Bertrand Jan 25 '13 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.