Search
Close this search box.
SQL-Server-2012
Picture of Ben Daland

Ben Daland

Simple SQL Server Version Control

Tracking changes for projects is very important and allows us to revert back to previous versions. There is out-of-the box software to handle source control, but not all environments are equipped or disciplined enough to handle full version control–especially SQL objects, which can be changed with a simple F5 while applications are running. In the past, I’ve only used TFS with a SQL Server Database Project, which works great, but it is disconnected and doesn’t stop another developer or DBA from updating an object directly. There are others like SQL Source Control from Redgate, which seem to be integrated with SQL Management tools, but is not free.

Recently, I’ve worked on a project where the client was looking for a simple way to automatically track changes for certain SQL objects across their 3 different environments (dev/qa/prod). The solution we used was a DDL Trigger to record certain events and store all revisions in a separate configuration DB that resided in each environment. Let’s review how this works:

First, we need to create a table to store all revisions:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
USE [Utility]
GO

CREATE TABLE [dbo].[AuditDDLEvent](
	[AuditEventSK] [int] IDENTITY(1,1) NOT NULL,
	[DatabaseName] [varchar](255) NULL,
	[SchemaName] [varchar](255) NULL,
	[ObjectName] [varchar](255) NULL,
	[ObjectType] [varchar](255) NULL,
	[EventDate] [datetime] NOT NULL,
	[EventType] [varchar](100) NULL,
	[EventDDL] [nvarchar](max) NULL,
	[UserCreated] [varchar](255) NOT NULL,
 CONSTRAINT [PK_AuditDDLEvent] PRIMARY KEY CLUSTERED 
(
	[AuditEventSK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[AuditDDLEvent] ADD  CONSTRAINT [DF_AuditDDLEvent_EventDate]  DEFAULT (getdate()) FOR [EventDate]
GO

ALTER TABLE [dbo].[AuditDDLEvent] ADD  CONSTRAINT [DF_AuditDDLEvent_UserCreated]  DEFAULT (suser_sname()) FOR [UserCreated]
GO

The table is created, so now we need to take current snapshots of the objects we’re going to be monitoring.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
USE [db name];

INSERT INTO Utility.dbo.AuditDDLEvent( 
	DatabaseName, 
	SchemaName, 
	ObjectName, 
	ObjectType, 
	EventDate, 
	EventType, 
	EventDDL, 
	UserCreated 
)
SELECT 
DB_NAME(), 
OBJECT_SCHEMA_NAME(o.[object_id]), 
o.name, 
CASE o.[type] 
	WHEN 'V' THEN 'VIEW'
	WHEN 'FN' THEN 'FUNCTION'
	WHEN 'IF' THEN 'FUNCTION'
	ELSE 'PROCEDURE'
END ObjectType,
o.create_date, 
'CREATE ' + CASE o.[type] 
	WHEN 'V' THEN 'VIEW'
	WHEN 'FN' THEN 'FUNCTION'
	WHEN 'IF' THEN 'FUNCTION'
	ELSE 'PROCEDURE'
END,
OBJECT_DEFINITION([object_id]) TEXT,
SUSER_SNAME()
FROM sys.objects o 
WHERE 
	o.[type] IN ('P', 'V', 'FN', 'IF') AND 
	o.is_ms_shipped = 0
ORDER BY 
	ObjectType,
	o.name;

Now we need to create a trigger to capture these events when they happen. Using the code below, create a DDL trigger on each DB you wish to monitor to record changes to procedures, functions, and views.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
USE [db name]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [DDLTrigger_Audit]
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
		CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
		CREATE_VIEW, ALTER_VIEW, DROP_VIEW
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EventData XML;
	SET @EventData = EVENTDATA();

	INSERT INTO [Utility].dbo.AuditDDLEvent
	( 
		DatabaseName, 
		SchemaName, 
		ObjectName, 
		ObjectType, 
		EventType, 
		EventDDL, 
		UserCreated
	)
	SELECT 
		DB_NAME(),
		@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'VARCHAR(255)'),  
		@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'VARCHAR(255)'), 
		@EventData.value('(/EVENT_INSTANCE/ObjectType)[1]',  'VARCHAR(255)'),
		@EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'VARCHAR(100)'), 
		@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
		SUSER_SNAME();
END
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [DDLTrigger_Audit] ON DATABASE
GO

ENABLE TRIGGER [DDLTrigger_Audit] ON DATABASE
GO

You are not bound to monitoring just procedures, functions and views. Click here for other DDL events.

Some other things to consider with this solution:

  • Adjusting the table and trigger to handle the rename event. This event is not available in SQL Server 2005 or below.
  • Adding validation to ignore changes that have no alterations, like when you hit F5 more than once, forgetting you’ve already executed your changes. I’ve done this myself many times.
  • Cleaning old versions after so many revisions if you only need to track the last # of changes and not the full history.

So how do you get the T-SQL out in a readable way? In SQL Server 2012 and above, carriage returns are preserved when copying data. If you are using an older SQL Server version, we can use the print function, but that has limitations. It truncates after 8000 bytes, which is 8000 chars or 4000 nchars. Below, is a stored procedure to print very large text.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
USE Utility
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[LongPrint]
	@string NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	
	SET @string = RTRIM( @string );

	DECLARE 
		@cr CHAR(1), 
		@lf CHAR(1),
		@len INT, 
		@cr_index INT, 
		@lf_index INT, 
		@crlf_index INT, 
		@has_cr_and_lf BIT, 
		@left NVARCHAR(4000), 
		@reverse NVARCHAR(4000)
		
	SET @cr = CHAR(13);
	SET @lf = CHAR(10); 
	SET @len = 4000;

	WHILE ( LEN( @string ) > @len )
		BEGIN
		   SET @left = LEFT( @string, @len );
		   SET @reverse = REVERSE( @left );
		   SET @cr_index = @len - CHARINDEX( @cr, @reverse ) + 1;
		   SET @lf_index = @len - CHARINDEX( @lf, @reverse ) + 1;
		   SET @crlf_index = CASE WHEN @cr_index < @lf_index THEN @cr_index ELSE @lf_index END;
		   SET @has_cr_and_lf = CASE WHEN @cr_index < @len and @lf_index < @len THEN 1 ELSE 0 END;
		   
		   PRINT( LEFT( @string, @crlf_index - 1 ) );
		   
		   SET @string = RIGHT( @string, LEN( @string ) - @crlf_index - @has_cr_and_lf );
		END

	PRINT( @string );
END

GO

This solution is not a prefect replacement for a full source control implementation. Due to its limitations and lack of functionality of other software, this solution might be better suited for small environments or as a temporary measure. But it is a simple approach to get you started.

Share this post

Check out these other must-read posts

Copyright © 2024 John Daniel Associates, Inc. All rights reserved.

Facebook Youtube