A simple code source control for SQL Server

If you use code objects like stored procedures or views on SQL Server, you will probably wonder how to save the code changes for source control. What happens if we want to go back to an earlier version, or just compare the current production version with the previous one?

There are several solutions. Microsoft has full source control support for SQL server database development with Visual Studio. It can control not only changes in SPs or Views but also changes to objects like new tables, new columns for existing tables, indexing, etc. You can save to a source control system, like Azure DevOps or GitHub, use branches, and do everything a full source control system does. This is a great option for development teams. For details, See Eitan Blomin’s blog – https://eitanblumin.com/2024/08/13/t-sql-tuesday-177-managing-database-code/

but sometimes it’s overkill. What if you just want to automatically back up changes to your code?

Today I want to show a very simple solution, based on a DDL trigger. This is by no means my invention, I based it on this blog post by Aaron Bertrand – https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

Before we start, a word on triggers. There are 2 types of triggers:

DML triggers

DML stands for data manipulation language. A trigger of this type can fire on changes to table rows (data) events, like inserting into a table, updating, or deleting. An example use case – when a row is added to the orders table, also update the number of open orders on the customer table.

DDL triggers

DDL (data definition language) triggers are started by events of object definition changes, like creating a table, changing a view definition, or adding a new user.

Creating the solution

So, after we became wiser about triggers, let’s see how this situation works. It has 2 components, an event table to hold the changes’ history, and a trigger that will fire on every change, and write the change to the event table.

First, let’s create the event table:

CREATE TABLE dbo.DDLEvents
(
    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    EventType    NVARCHAR(64),
    EventDDL     NVARCHAR(MAX),
    EventXML     XML,
    DatabaseName NVARCHAR(255),
    SchemaName   NVARCHAR(255),
    ObjectName   NVARCHAR(255),
    HostName     VARCHAR(64),
    IPAddress    VARCHAR(48),
    ProgramName  NVARCHAR(255),
    LoginName    NVARCHAR(255)
);

Now, to create a baseline, let’s copy the current views and stored procedures to the event table. If you are just starting with SQL server, and don’t have any code objects yet, you can skip this part.

--Stored Procedures
INSERT dbo.DDLEvents
(
    EventType,
    EventDDL,
    DatabaseName,
    SchemaName,
    ObjectName,
    LoginName
)
SELECT
    'CREATE_PROCEDURE',
    OBJECT_DEFINITION([object_id]),
    DB_NAME(),
    OBJECT_SCHEMA_NAME([object_id]),
    OBJECT_NAME([object_id]),
    SUSER_NAME()
FROM
    sys.procedures;
Go

--Views
INSERT dbo.DDLEvents
(
    EventType,
    EventDDL,
    DatabaseName,
    SchemaName,
    ObjectName,
    LoginName
)
SELECT
    'CREATE_VIEW',
    OBJECT_DEFINITION([object_id]),
    DB_NAME(),
    OBJECT_SCHEMA_NAME([object_id]),
    OBJECT_NAME([object_id]),
    SUSER_NAME()
FROM
    sys.views;
Go

And the next piece of code is the actual trigger. please note that creating the trigger automatically enables it.

CREATE TRIGGER DDLTrigger_code_save
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
		CREATE_VIEW, ALTER_VIEW, DROP_VIEW
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @EventData XML = EVENTDATA();
 
    DECLARE @ip varchar(48) = CONVERT(varchar(48), 
        CONNECTIONPROPERTY('client_net_address'));
 
    INSERT dbo.DDLEvents
    (
        EventType,
        EventDDL,
        EventXML,
        DatabaseName,
        SchemaName,
        ObjectName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData,
        DB_NAME(),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        HOST_NAME(),
        @ip,
        PROGRAM_NAME(),
        SUSER_SNAME();
END

From now on, every change to a view or a stored procedure will be registered to the event table.

Testing the solution

let’s test this. The following code creates a view and a stored procedure, updates them, and then deletes (drops) them. Then we’ll see the code changes reflected in the table.

create view dbo.test_v
as
select 1 as col;
go

alter view dbo.test_v
as
select 2 as col;
go

drop view dbo.test_v;
go

create proc dbo.usp_test
as
select 1 as col;
go

alter proc dbo.usp_test
as
select 2 as col;
go

drop proc dbo.usp_test;
go

Run this select to view the results in the events table:

--views the code changes in the event table
select 
	ObjectName,
	EventDate,
	EventType,
	EventDDL
from dbo.DDLEvents
where ObjectName like '%test%'
order by EventDate desc

The select query results are below. Note that there are more columns in the event table, with information about who executes the code and form where, which I didn’t include in the results for privacy.

That’s all. As I said, a simple solution, but it works great. You can get the SQL script with all the commands at our GitHub repository – https://github.com/MadeiraData/microsoft-data-engineers-club/tree/main/SQL%20Server/Trigger%20source%20control

Let me know in the comments how it’s working for you, and if you have other solutions for SQL server source control!