Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 http://stackoverflow.com/questions/14522224/how-to-keep-history-of-sql-server-stored-procedure-revisions first cos I suspect it would get more views there.

share|improve this question
7  
Stored procedures are source code for your application. They have to be stored in real version control system (subversion, git, ...) –  a_horse_with_no_name Jan 25 '13 at 13:11
1  
No, there's no other way to keep history of database code except if you script them yourself. This is where a versioning system comes handy. No db platform has a history of all its versions of procedures/triggers/etc. It would mean to implement a versioning system inside its own platform, and that is already a complex one. –  Marian Jan 25 '13 at 13:40
2  
again: this is exactly what version control system is for. If your code is worth anything, there is no other way. –  a_horse_with_no_name Jan 25 '13 at 14:12
2  
@cja: what programmer would refuse a versioning system? –  Marian Jan 25 '13 at 14:12
2  
@cja SQL Server specializes in data storage and does it well. Other tools specialize in version control and do it well. If you want one product to solve all problems, you might get it - but the solutions will never be as good as specialized ones. –  AlexKuznetsov Jan 25 '13 at 19:15
show 18 more comments

2 Answers

up vote 10 down vote accepted

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/

share|improve this answer
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
    
Thank you. Accepting as answer pending testing. –  cja Jan 25 '13 at 14:29
add comment

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.

share|improve this answer
    
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
    
+1 using version control is a must –  AlexKuznetsov Jan 25 '13 at 14:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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