Controle de versão dentro do SQL Server

Julia : Então, quem mudou meu procedimento ontem?
Lesha : não eu
Maxim : não eu
- Pessoal, podemos pegar o Git?
Seryozha : está na hora!
2 semanas se passaram ...

Julia : gente?
- Sim, você não se comprometeu?
Julia : caramba não (...

Foi assim que tudo começou. Bem, o que, cada caractere e cada linha confirmam?

Ou talvez tudo isso aconteça por conta própria?) Nesse ponto, eles começam a vir à mente
Gatilhos DDL , tabela temporal e imagem são adicionados. Resolvido, vamos armazenar versões dentro
SQL Server'a !)




Primeiro, crie tabelas nas quais as versões serão armazenadas

USE master GO --     IF NOT EXISTS ( SELECT 1 FROM sys.objects WHERE name = 'VersionControlHistory' AND type = 'U' ) CREATE TABLE dbo.VersionControlHistory( Id INT NOT NULL, Event sysname NOT NULL, Db sysname NOT NULL, Sch sysname NOT NULL, Object sysname NOT NULL, Sql XML NOT NULL, Login sysname NOT NULL, StartDate DATETIME2(0) NOT NULL, EndDate DATETIME2(0) NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO --      IF NOT EXISTS ( SELECT 1 FROM sys.objects WHERE name = 'VersionControl' AND type = 'U' ) CREATE TABLE dbo.VersionControl( Id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_VersionControl PRIMARY KEY NONCLUSTERED, Event sysname NOT NULL, Db sysname NOT NULL, Sch sysname NOT NULL, Object sysname NOT NULL, Sql XML NOT NULL, Login sysname NOT NULL, StartDate DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL, EndDate DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL, PERIOD FOR SYSTEM_TIME (StartDate, EndDate) ) WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.VersionControlHistory ) ) GO 

É importante ter em mente as limitações da tabela temporal.

1. Após criá-los, você não poderá aplicar comandos DDL às tabelas principal ou histórica. E você não pode excluir a tabela Temporal
2. Você não pode alterar dados na tabela histórica

A segunda restrição nos convém, mas o que fazer com a primeira?

O algoritmo é o seguinte:

 --        ALTER TABLE dbo.VersionControl SET ( SYSTEM_VERSIONING = OFF ); /* -  */ --    : ALTER TABLE dbo.VersionControl SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.VersionControlHistory, DATA_CONSISTENCY_CHECK = OFF ); 

Embora ainda não haja índices na tabela, preencha-os com nossos procedimentos, funções etc. marcados como INIT , o que, no nosso caso, significará o posicionamento inicial

 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(''<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 @databases TABLE (rn INT, Name sysname); INSERT @databases (rn, Name) SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn, name FROM sys.databases WHERE owner_sid != 0x01; DECLARE @i INT = 1, @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 mean one of [', @db, '] object is invalid for convert to XML' ); PRINT @error; SET @i += 1; CONTINUE; END CATCH; END; GO 

Porque alterações nos objetos ocorrerão com a instrução UPDATE e, na maioria das vezes, examinaremos as versões por chave: o banco de dados, o esquema e o nome do objeto, o índice implora!

 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); 

Tudo está pronto para começar a armazenar versões e nos ajudará com este DDL Trigger

Importante! Porque tabelas para versões estão no banco de dados mestre , depois de criar um gatilho, todos os que não têm direitos nesse banco de dados não podem modificar, criar ou excluir objetos

 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 --   /*      : 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, RENAME AS BEGIN SET 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)' ) )) 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 

E para a conveniência de usar este sistema, o procedimento abaixo é proposto.

É fácil de usar. O prefixo sp_ nos ajudará a acessar o procedimento sem especificar o banco de dados e o esquema. Os parâmetros são preenchidos intuitivamente. Você pode especificar apenas o banco de dados e veremos objetos associados a ele o tempo todo, mas também é possível usar o esquema, o próprio objeto e, é claro, o intervalo de tempo para o qual as alterações foram feitas.

 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 

Abaixo estão exemplos de como usar o procedimento

 --        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'; 

Você pode instalar essa microfrabalho no meu repositório e, se sua versão do SQL Sever for menor que 2016, aqui está você. A propósito, estamos usando esta versão agora, mas não é tão legal.

Em conclusão

Eu nunca consegui derrotar a conclusão & _gt; e & _lt; em vez dos sinais > e < da tabela master.dbo.VersionControl pelo campo Sql . Se você puder ajudar com isso ou tiver alguma idéia, estou aguardando uma solicitação de recebimento .

Obrigado pelo seu tempo, coloque estrelas, corações e setas para cima.