Version Control Inside SQL Server

Yulia : So, who changed my procedure yesterday?
Lyosha : not me
Maxim : not me
- Guys, can we get Git?
Seryozha : it's time!
2 weeks have passed ...

Yulia : children?
- Yul, and you did not commit?
Yulia : no damn (...

That's how it all started. Well, what, every character and every line of commit?

Or maybe it will all happen by itself?) At this point,
DDL-triggers start to come to mind , Temporal table and the picture develops. Resolved, we will store versions inside
SQL Server !)




To begin with we create tables in which versions will be stored

USEmasterGO-- Сначала создаем историческую таблицуIFNOTEXISTS (
	SELECT1FROM sys.objects
	WHEREname = 'VersionControlHistory'ANDtype = 'U'
)
	CREATETABLE dbo.VersionControlHistory(
		IdINTNOTNULL,
		Event sysname NOTNULL,
		Db sysname NOTNULL,
		Sch sysname NOTNULL,
		Object sysname NOTNULL,
		SqlXMLNOTNULL,
		Login sysname NOTNULL,
		StartDate DATETIME2(0) NOTNULL,
		EndDate DATETIME2(0) NOTNULL
	)  ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO-- А затем таблицу журнала измененийIFNOTEXISTS (
	SELECT1FROM sys.objects
	WHEREname = 'VersionControl'ANDtype = 'U'
)
	CREATETABLE dbo.VersionControl(
		IdINTIDENTITY(1,1) NOTNULLCONSTRAINT PK_VersionControl 
			PRIMARY KEY NONCLUSTERED,
		Event sysname NOTNULL,
		Db sysname NOTNULL,
		Sch sysname NOTNULL,
		Object sysname NOTNULL,
		SqlXMLNOTNULL,
		Login sysname NOTNULL,
		StartDate DATETIME2(0) GENERATEDALWAYSASROWSTARTNOTNULL,
		EndDate DATETIME2(0) GENERATEDALWAYSASROWENDNOTNULL,
		PERIODFOR SYSTEM_TIME (StartDate, EndDate)
	) WITH ( 
		SYSTEM_VERSIONING = ON (
			HISTORY_TABLE = dbo.VersionControlHistory
		)
	)
GO

It is important to keep in mind the limitations of the Temporal table.

1. After creating them, you cannot use the DDL command either on the main table or on the historical tables. And you can not delete Temporal table
2. You can not change the data in the historical table

The second limitation suits us, but what to do with the first?

The algorithm is as follows:

-- Для начала отключаем версионирование на основной таблицеALTERTABLE dbo.VersionControl SET ( SYSTEM_VERSIONING = OFF );
/*
    что-то делаем
*/--И снова включаем поддержку изменений:ALTERTABLE dbo.VersionControl SET ( 
	SYSTEM_VERSIONING = ON ( 
		HISTORY_TABLE = dbo.VersionControlHistory, 
		DATA_CONSISTENCY_CHECK = OFF
	);

While there are no indexes on the table yet, we will fill it with our procedures, functions, etc., marked INIT , which in our case will mean initial placement

DECLARE @queryNVARCHAR(MAX),
	@templateNVARCHAR(MAX) = N'
USE [db]
INSERT INTO MASTER.dbo.VersionControl WITH (TABLOCKX) (
	Event, Db, Sch, Object, Sql, Login
) 
	SELECT ''INIT'' AS Event,
		DB_NAME(),
		ss.name AS Sch,
		so.name AS Object,
		CONCAT(''<query><![CDATA['', sasm.definition, '']]></query>'' ),
		SUSER_SNAME() AS Login
	FROM sys.objects AS so
		JOIN sys.schemas AS ss ON ss.schema_id = so.schema_id
		JOIN sys.all_sql_modules AS sasm ON sasm.object_id = so.object_id
	WHERE so.is_ms_shipped = 0
		AND NOT EXISTS (
			SELECT 1
			FROM MASTER.dbo.VersionControl AS vc
			WHERE vc.Db = ''[db]''
				AND vc.Sch = ss.name
				AND vc.Object = so.name
			);
';
DECLARE @databasesTABLE (rn INT, Name sysname);
	INSERT @databases (rn, Name)
		SELECT ROW_NUMBER() OVER (ORDERBY (SELECTNULL)) AS rn, nameFROM sys.databases
		WHERE owner_sid != 0x01;
DECLARE @i INT = 1, 
	@maxINT = (SELECTMAX(rn) FROM @databases),
	@errorNVARCHAR(128),
	@db sysname;
WHILE @i < @max BEGINSELECT @query = REPLACE(@template, '[db]', Name),
		@db = NameFROM @databasesWHERE rn = @i;
	BEGIN TRY
		EXECUTE sp_executesql @query;
		SET @i += 1;
		CONTINUE;
	END TRY
	BEGIN CATCH
		SET @error = CONCAT(
			'XML Parsing error. In this case that''s mean one of [', 
			@db, '] object is invalid for convert to XML'
		);
		PRINT @error;
		SET @i += 1;
		CONTINUE;
	END CATCH;
END;
GO

Since Changes to objects will occur with the UPDATE statement , and we will most often look at the versions by key: database, schema, and object name, the index suggests!

IF NOT EXISTS (
	SELECT1FROM sys.indexes
	WHEREname = 'IX_VersionControl_upd_key'
)
	CREATEUNIQUE NONCLUSTERED INDEX IX_VersionControl_upd_key 
		ON MASTER.dbo.VersionControl (Db, Sch, Object)
		INCLUDE (Sql, Event, Login);

Everything is ready to start storing versions and help us with this DDL Trigger.

Important! Since tables for versions are in the master database , after creating the trigger, all those who do not have rights to this database will not be able to change, create and delete objects

IF EXISTS (
	SELECT1FROM sys.server_triggers
	WHEREname = 'tr_VersionControl'
)
	DROPTRIGGER tr_VersionControl ON ALL SERVERGOCREATETRIGGER tr_VersionControl ON ALL SERVER--WITH ENCRYPTION -- по желанию/*	Указываем отлавливаемые события
	полный список событий: 
		https://docs.microsoft.com/ru-ru/sql/relational-databases/triggers/ddl-events?view=sql-server-2017
*/FOR
	CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
	CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
	CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
	CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
	CREATE_ASSEMBLY, ALTER_ASSEMBLY, DROP_ASSEMBLY,
	CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
	CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
	RENAMEASBEGINSET NOCOUNT ON;
	UPDATE vs
	SET vs.Event =  ev.EventType,
		vs.Sql = CONCAT('<query><!CDATA', ev.Sql, '></query>' ),
		vs.Login = ev.Login
	FROM MASTER.dbo.VersionControl AS vs
		JOIN (
			SELECT * 
			FROM ( VALUES (
				EVENTDATA().value(
		'(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(128)'
				),
				EVENTDATA().value(
		'(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)'
				),
				EVENTDATA().value(
		'(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)'
				),
				EVENTDATA().value(
		'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)'
				),
				EVENTDATA().value(
		'(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)'
				)
			)) ASEvent (EventType, Sch, Object, Sql, Login ) 
		) ev ON vs.Db = DB_NAME()
			AND vs.Sch = ev.Sch
			AND vs.Object = ev.Object
	;
ENDGO

And for ease of use of this system the procedure is proposed below.

It is easy to use. The sp_ prefix will help us access the procedure without specifying the database and schema. Parameters are filled intuitively. You can specify only the database and we will see objects associated only with it for all the time, but it is possible both the schema and the object itself and of course the time range for which changes were made.

CREATEPROCEDURE dbo.sp_Vc 
	@db sysname = '%', 
	@sch sysname = '%',
	@obj sysname = '%',
	@from DATETIME2(0) = NULL,
	@to DATETIME2(0) = NULLASBEGINSET NOCOUNT ON;
	IF @from IS NULL AND @to IS NULL BEGINSELECT *
		FROM master.dbo.VersionControl
		WHERE Db LIKE @db
			AND Sch LIKE @sch
			ANDObjectLIKE @obj
		ORDERBY StartDate DESCENDELSEBEGINSELECT *
		FROM master.dbo.VersionControl 
			FOR SYSTEM_TIME BETWEEN @fromAND @toWHERE Db LIKE @db
			AND Sch LIKE @sch
			ANDObjectLIKE @obj
		ORDERBY StartDate DESCENDENDGO

Below are examples of using the procedure.

-- вывод всей таблицы изменений за всё время
sp_Vc;
/* вывод изменений по определенной базе данных 
	за всё время */
sp_Vc 'dwh';
/* вывод изменений по определенной базе данных и схеме
	за всё время */
sp_Vc 'dwh', 'dbo';
/* вывод изменений по определенной базе данных, схеме
	и конкретному объекту за всё время */
sp_Vc 'dwh', 'dbo', 'MyObject';
/* вывод изменений по определенной базе данных, схеме,
	и конкретному объекту за период с 1-го по 9-е мая */
sp_Vc 'dwh', 'dbo', 'MyObject', '20180501 00:00:00', '20180509 00:00:00';

You can install this microform from my repository , and if your version of SQL Sever is under 2016, then you should go here . By the way, we are using this version now, but it is not so cool.

Finally

I never managed to defeat the conclusion & _gt; and & _lt; instead of the > and < signs from the master.dbo.VersionControl table by the Sql field . If you can help with this or you have ideas, wait for Pull Request .

Thank you for your time, put stars, hearts and arrows up.