Version control inside SQL Server'a

Julia : So, who changed my procedure yesterday?
 
Lyosha : not I
 
Maxim : not I
 
- Guys, can Git get?
 
Серёжа : It is high time!
 
2 weeks have passed
 
 
Julia : Rebyyayat?
 
- Yul, and you did not commit?
 
Julia : damn no ( .
 
 
That's how it all began. Well and what, each character and each line to commit?
 
And maybe all this will happen by itself?) At this moment
begin to come to mind.  
DDL triggers , Temporal table and the picture is formed. It is solved, we will store versions inside
 
SQL Server'a !)
 
Version control inside SQL Server'a  
 
Temporal table
 
 
After creating them, you can not use DDL commands to either the main or historical tables. And you can not delete Temporal table
 
You can not modify the data in the historical table
 
 
The second restriction suits us, and what to do with the first?
 
The algorithm is as follows:
 
- For starters we disable versioning on the main table
ALTER TABLE dbo.VersionControl SET (SYSTEM_VERSIONING = OFF);
/*
something is doing
* /
- And again turn on support for the changes:
ALTER TABLE dbo.VersionControl SET ( ? SYSTEM_VERSIONING = ON (
HISTORY_TABLE = dbo.VersionControlHistory,
DATA_CONSISTENCY_CHECK = OFF
);

 
 
While there are no indexes on the table, we will fill it with our procedures, functions, etc. with the mark [b] INIT
, which in our case will mean the initial placement of
 
DECLARE @query NVARCHAR (MAX),
@template NVARCHAR (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 (''
'', Sasm.definition, ''
''),
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 @databases TABLE (rn INT, Name sysname);
INSERT @databases (rn, Name)
SELECT ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) rn, name
FROM sys.databases
WHERE owner_sid! = 0x01;
DECLARE @i INT = ?
@max INT = (SELECT MAX (rn) FROM @databases),
@error NVARCHAR (128),
@db sysname;
WHILE @i < @max BEGIN
SELECT @query = REPLACE (@template, '[db]', Name),
@db = Name
FROM @databases
WHERE 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 the mean of[',
@db, ']Object is invalid for convert to XML '
);
PRINT @error;
SET @i + = 1;
CONTINUE;
END CATCH;
END;
GO

 
 
Because the objects will be changed by the instruction UPDATE , and we will most often look at the version by the key: the database, the schema and the name of the object, the index suggests!
 
IF NOT EXISTS (
Select 1
FROM sys.indexes
WHERE name = 'IX_VersionControl_upd_key'
)
CREATE UNIQUE NONCLUSTERED INDEX IX_VersionControl_upd_key
ON MASTER.dbo.VersionControl (Db, Sch, Object)
INCLUDE (Sql, Event, Login);

 
 
Everything is ready to start storing the versions and will help us in this DDL-Trigger
 
[b] Important!
Because tables for versions are in the database master , after creating the trigger, all who do not have rights to this database, they can not change, create or delete objects
 
IF EXISTS (
Select 1
FROM sys.server_triggers
WHERE name = 'tr_VersionControl'
)
DROP TRIGGER tr_VersionControl ON ALL SERVER
GO
CREATE TRIGGER tr_VersionControl ON ALL SERVER
--WITH ENCRYPTION - at will
/* Specify the captured events
full list of events:
https://docs.microsoft.com/en-us/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,
RENAME
AS
BEGIN
SET NOCOUNT ON;
UPDATE vs
SET vs.Event = ev.EventType,
vs.Sql = CONCAT ('
'),
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) '
)
)) AS Event (EventType, Sch, Object, Sql, Login)
) ev ON vs.Db = DB_NAME (
). AND vs.Sch = ev.Sch
AND vs.Object = ev.Object
;
END
GO

 
 
And for the convenience of using this system, the procedure below is suggested.
 
It's easy to use. The prefix is ​​ sp_ will help us to refer to the procedure without specifying a database and schema. The parameters are filled in intuitively. You can specify only the database and we will see objects that are associated only with it for the whole time, and you can both the schema, the object itself and, of course, the time range for which the changes were made
 

CREATE PROCEDURE dbo.sp_Vc
@db sysname = '%',
@sch sysname = '%',
@obj sysname = '%',
@from DATETIME2 (0) = NULL,
@to DATETIME2 (0) = NULL
AS
BEGIN
SET NOCOUNT ON;
IF @from IS NULL AND @to IS NULL BEGIN
SELECT *
FROM master.dbo.VersionControl
WHERE Db LIKE @db
AND Sch LIKE @sch
AND Object LIKE @obj
ORDER BY StartDate DESC
END
ELSE BEGIN
SELECT *
FROM master.dbo.VersionControl
FOR SYSTEM_TIME BETWEEN @from AND @to
WHERE Db LIKE @db
AND Sch LIKE @sch
AND Object LIKE @obj
ORDER BY StartDate DESC
END
END
GO

 
 
Below are examples of using the procedure
 
- Display the entire table of changes for the entire time
sp_Vc;
/* output changes on a specific database
for all time * /
sp_Vc 'dwh';
/* output changes on a specific database and the scheme
for all time * /
sp_Vc 'dwh', 'dbo';
/* output changes on a specific database, the scheme
and a specific object for all time * /
sp_Vc 'dwh', 'dbo', 'MyObject';
/* output changes on a specific database, the scheme,
and a specific object for the period from May 1st to 9th * /
sp_Vc 'dwh', 'dbo', 'MyObject', '???:00:00', '???:00:00';

 
 
You can install this microframe from my repository , and if your version of SQL Sever is younger than 201? then you need here . By the way, we now use this version, but it's not so cool =)
 
 

In conclusion,


 
I never managed to beat pin & _gt; and & _lt; instead of the signs > and < from the table master.dbo.VersionControl over the field Sql . If you can help with this or you have ideas, I'm waiting for Pull Request'a
 
Thank you for taking the time, put the asterisks, hearts and arrows up =)
+ 0 -

Add comment