Como criar um Controle de Versão de Procedures, Views e Functions no SQL Server

11 de abril de 201016 de dezembro de 2016Fabrício LimaAdministração de Banco de Dados, Virtual PASS BR

Quantas vezes já te perguntaram qual era o código de uma função, procedure ou view em uma determinada data?

Até o SQL Server 2000, só era possível obter essa informação através da geração periódica de arquivos com scripts dos objetos do banco de dados. Entretanto, a partir do SQL Server 2005, esse procedimento melhorou com a nova funcionalidade das Triggers DDL(Data Definition Language). Nesse artigo, mostrarei como montar um Controle de Versão de objetos do banco de dados, tais como, funções, procedures, views, tabelas, índices e etc. O procedimento é muito simples de se implementar e é de muita importância para um ambiente de banco de dados. Portanto, se você ainda não guarda essas informações, aconselho que implemente esse Controle de Versão de objetos assim que possível em seu ambiente.

Inicialmente, devemos gerar um script de nossa database para armazenar as informações dos objetos da forma como eles estão nesse momento. Para fazer isso, basta ir no Object Explore do Management Studio, clicar com o botão direito sobre uma database -> Tasks – > Generate Scripts , depois é só seguir o Wizard marcando a opção de gerar o script para todos os objetos dessa database e salvar o script em um arquivo.

Esse procedimento deve ser realizado para todas as databases que você queira guardar as informações.

Nesse momento, temos a versão de todos os objetos das databases de nosso banco de dados. Para guardar as alterações futuras criaremos uma tabela e uma trigger DDL que irá inserir registros em nossa tabela com as novas versões dos objetos.

Mas Fabrício, e as alterações que já foram realizadas, eu não consigo descobrir?
Infelizmente não. O controle de versão passará a valer somente a partir de sua implementação, só a partir desse momento que saberemos como um objeto estava em qualquer dia e hora que precisarmos.

Para criar a tabela, deve ser escolhida uma database para rodar o script abaixo. No meu caso, a tabela ficará armazenada em uma database chamada FabricioLima e esse nome deve ser alterado para o nome de uma database do seu ambiente.

CREATE TABLE [FabricioLima].[dbo].[Trace_Alteracao_Objeto] (
	[Id_Trace_Alteracao_Objeto] [INT] IDENTITY(1,1) NOT NULL,
	[Tp_Evento] [VARCHAR](30) NULL,
	[Dt_Alteracao] [DATETIME] NULL,
	[Nm_Servidor] [VARCHAR](100) NULL,
	[Nm_Login] [VARCHAR](50) NULL,
	[Nm_Database] [VARCHAR](20) NULL,
	[Nm_Objeto] [VARCHAR](50) NULL,
	[Ds_Evento] [XML] NULL
) ON [PRIMARY]

CREATE TABLE [FabricioLima].[dbo].[Trace_Alteracao_Objeto] (

[Id_Trace_Alteracao_Objeto] [INT] IDENTITY(1,1) NOT NULL,

[Tp_Evento] [VARCHAR](30) NULL,

[Dt_Alteracao] [DATETIME] NULL,

[Nm_Servidor] [VARCHAR](100) NULL,

[Nm_Login] [VARCHAR](50) NULL,

[Nm_Database] [VARCHAR](20) NULL,

[Nm_Objeto] [VARCHAR](50) NULL,

[Ds_Evento] [XML] NULL

) ON [PRIMARY]

Logo em seguida, criaremos a trigger a nível de database que enviará os dados para a nossa tabela. Essa trigger deve ser criada em todas as databases que você queira fazer o controle de versão dos objetos.

CREATE TRIGGER [trgTrace_Alteracao_Objeto]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    SET NOCOUNT ON
    SET ARITHABORT ON

    DECLARE @Evento XML
    SET @Evento = EVENTDATA()
    
    INSERT INTO [FabricioLima].[dbo].[Trace_Alteracao_Objeto] ( [Tp_Evento], [Dt_Alteracao], [Nm_Servidor], [Nm_Login], [Nm_Database], [Nm_Objeto], [Ds_Evento] )
    SELECT  @Evento.value('(/EVENT_INSTANCE/EventType/text())[1]','VARCHAR(50)') [Tipo_Evento],
            @Evento.value('(/EVENT_INSTANCE/PostTime/text())[1]','DATETIME') [PostTime],
            @Evento.value('(/EVENT_INSTANCE/ServerName/text())[1]','VARCHAR(50)') [ServerName],
            @Evento.value('(/EVENT_INSTANCE/LoginName/text())[1]','VARCHAR(50)') [LoginName],
            @Evento.value('(/EVENT_INSTANCE/DatabaseName/text())[1]','VARCHAR(50)') [DatabaseName],
            @Evento.value('(/EVENT_INSTANCE/ObjectName/text())[1]','VARCHAR(50)') [ObjectName], 
            @Evento
END

CREATE TRIGGER [trgTrace_Alteracao_Objeto]

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

BEGIN

SET NOCOUNT ON

SET ARITHABORT ON

DECLARE @Evento XML

SET @Evento = EVENTDATA()

INSERT INTO [FabricioLima].[dbo].[Trace_Alteracao_Objeto] ( [Tp_Evento], [Dt_Alteracao], [Nm_Servidor], [Nm_Login], [Nm_Database], [Nm_Objeto], [Ds_Evento] )

SELECT @Evento.value('(/EVENT_INSTANCE/EventType/text())[1]','VARCHAR(50)') [Tipo_Evento],

@Evento.value('(/EVENT_INSTANCE/PostTime/text())[1]','DATETIME') [PostTime],

@Evento.value('(/EVENT_INSTANCE/ServerName/text())[1]','VARCHAR(50)') [ServerName],

@Evento.value('(/EVENT_INSTANCE/LoginName/text())[1]','VARCHAR(50)') [LoginName],

@Evento.value('(/EVENT_INSTANCE/DatabaseName/text())[1]','VARCHAR(50)') [DatabaseName],

@Evento.value('(/EVENT_INSTANCE/ObjectName/text())[1]','VARCHAR(50)') [ObjectName],

@Evento

END

Vamos aos testes. Execute os procedimentos abaixo para verificar se os mesmos serão registrados.

CREATE PROCEDURE [dbo].[stpControle_versao] AS SELECT 'Versão 1.0'

GO
ALTER PROCEDURE [dbo].[stpControle_versao] AS SELECT 'Versão 2.0'

GO
DROP PROCEDURE [dbo].[stpControle_versao]

GO
CREATE FUNCTION [dbo].[fncControle_versao]() RETURNS VARCHAR(10) AS  BEGIN RETURN 'Versão 1.0' END

GO
ALTER FUNCTION [dbo].[fncControle_versao]() RETURNS VARCHAR(10) AS  BEGIN RETURN 'Versão 2.0' END

GO
DROP FUNCTION [dbo].[fncControle_versao]

CREATE PROCEDURE [dbo].[stpControle_versao] AS SELECT 'Versão 1.0'

ALTER PROCEDURE [dbo].[stpControle_versao] AS SELECT 'Versão 2.0'

DROP PROCEDURE [dbo].[stpControle_versao]

CREATE FUNCTION [dbo].[fncControle_versao]() RETURNS VARCHAR(10) AS BEGIN RETURN 'Versão 1.0' END

ALTER FUNCTION [dbo].[fncControle_versao]() RETURNS VARCHAR(10) AS BEGIN RETURN 'Versão 2.0' END

DROP FUNCTION [dbo].[fncControle_versao]

Para conferir as informações do controle de versão, basta executar a query abaixo.

SELECT [Tp_Evento], [Dt_Alteracao], [Nm_Servidor], [Nm_Login], [Nm_Database], [Nm_Objeto], [Ds_Evento]
FROM [FabricioLima].[dbo].[Trace_Alteracao_Objeto] WITH(NOLOCK)
ORDER BY [Dt_Alteracao]

SELECT [Tp_Evento], [Dt_Alteracao], [Nm_Servidor], [Nm_Login], [Nm_Database], [Nm_Objeto], [Ds_Evento]

FROM [FabricioLima].[dbo].[Trace_Alteracao_Objeto] WITH(NOLOCK)

ORDER BY [Dt_Alteracao]

Essa query retorna o seguinte resultado.

Podemos verificar que a criação, alteração e a exclusão dos dois objetos foram registradas com a data da alteração e o Login de quem alterou.

Segue um exemplo da utilização do controle de versão:

Imagine que foi gerado um script de uma database chamada FabricioLima no dia 01/01/2010.
Essa database possui uma SP chamada stpControle_Versao, onde a mesma foi alterada nos dias 01/02/2010, 01/03/2010 e 01/04/2010.
Nos dias em que o objeto foi alterado, a trigger inseriu uma linha na tabela de trace com as alterações.

Abaixo mostro onde encontrar a versão procurada de acordo com uma determinada necessidade:

Preciso de saber como a SP estava no dia 10/01/2010: Está no Script gerado dia 01/01/2010
Preciso de saber como a SP estava no dia 30/01/2010: Está no Script gerado dia 01/01/2010
Preciso de saber como a SP estava no dia 20/02/2010: Está na tabela Trace_Alteracao_Objeto com Dt_Alteracao = 01/02/2010
Preciso de saber como a SP estava no dia 20/03/2010: Está na tabela Trace_Alteracao_Objeto com Dt_Alteracao = 01/03/2010
Preciso de saber como a SP estava no dia 05/04/2010: Como a última alteração foi no dia 01/04/2010, o código da SP no dia 05/04/2010 é o código atual da SP.

Assim, quando alguém te solicitar o script de um objeto, basta executar a query abaixo para recuperar todas as alterações nesse objeto.

SELECT *
FROM [dbo].[Trace_Alteracao_Objeto] WITH(NOLOCK)
WHERE [Nm_Objeto] = 'Nome_Objeto'
ORDER BY [Dt_Alteracao]

SELECT *

FROM [dbo].[Trace_Alteracao_Objeto] WITH(NOLOCK)

WHERE [Nm_Objeto] = 'Nome_Objeto'

ORDER BY [Dt_Alteracao]

Caso nenhum resultado seja retornado, esse objeto nunca foi alterado desde a implementação do Controle de Versão.

Depois de encontrada a versão desejada na tabela, para visualizar seu código basta clicar sobre a coluna Ds_Evento dessa linha. Uma nova janela é aberta com o código XML do evento que foi executado, conforme podemos ver abaixo.

<EVENT_INSTANCE>
  <EventType>ALTER_PROCEDURE</EventType>
  <PostTime>2010-04-11T10:51:44.483</PostTime>
  <SPID>51</SPID>
  <ServerName>FABRICIO</ServerName>
  <LoginName>FABRICIO\Fabriciol1</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>FabricioLima</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>stpControle_versao</ObjectName>
  <ObjectType>PROCEDURE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>ALTER PROCEDURE stpControle_versao AS SELECT 'Versão 2.0'
    </CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

<EVENT_INSTANCE>

<EventType>ALTER_PROCEDURE</EventType>

<ServerName>FABRICIO</ServerName>

<LoginName>FABRICIO\Fabriciol1</LoginName>

<DatabaseName>FabricioLima</DatabaseName>

<ObjectName>stpControle_versao</ObjectName>

<ObjectType>PROCEDURE</ObjectType>

<CommandText>ALTER PROCEDURE stpControle_versao AS SELECT 'Versão 2.0'

</CommandText>

</TSQLCommand>

</EVENT_INSTANCE>

O código do objeto fica entre as tags <CommandText> e </CommandText> na mesma formatação utilizada na criação ou alteração do objeto.

Caso os objetos possuam os caracteres ‘>’ e ‘<‘ em seu script, eles serão substituídos no campo XML pelas strings ‘>’ e ‘<’ respectivamente. Logo, caso você precise subir uma versão antiga de um objeto que possua esses caracteres, deve ser realizado um replace no script do objeto como abaixo:

Copie o texto entre as tags <CommandText> </CommandText>
Abra uma nova query e cole o texto.
Tecle Ctrl+H e realize o replace de ‘>’ por ‘>’ de todo o texto.
Tecle Ctrl+H e realize o replace de ‘<’ por ‘<‘ de todo o texto.
Tecle F5 para subir o objeto.

Agora que as informações de criação, alteração e exclusão de objetos estão armazenadas em seu banco de dados, é possível adicionar mais uma informação a planilha do meu primeiro artigo Criando um CheckList Automático do Banco de Dados. No meu ambiente existe uma aba dessa planilha com as alterações de objetos realizadas no dia anterior, assim, acompanho diariamente todos os objetos que são alterados no meu Banco de Dados.

Desde quando me mostraram essa nova funcionalidade do SQL Server 2005 eu implementei esse Controle de Versão dos objetos no meu banco de dados. Até hoje, já perdi as contas do número de vezes que me solicitaram essa informação, além de identificar os culpados de alterar um objeto de forma errônea.

Espero ter ajudado.

Gostou desse Post?

Cadastre seu e-mail para receber novos Posts e curta minha Página no Facebook para receber Dicas de Leituras e Eventos sobre SQL Server.

Abraços,

Fabrício Lima

MCITP – Database Administrator

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006

Relacionado

11 thoughts on “Como criar um Controle de Versão de Procedures, Views e Functions no SQL Server”

Felipe disse:

12 de abril de 2010 às 14:42

Boa Fabrício, muito útil esse controle de versão, o blog estar com ótimo conteúdo, Parabéns !

Responder
Fabrício disse:

12 de abril de 2010 às 15:42

Valeu Felipe.

Responder
X B disse:

3 de maio de 2010 às 12:39

Muito bom Fabrício !Uma dúvida:Assumi que meu banco foi criado hoje (base 100 = hoje) como faço pra gerar o DDL command dos objetos e salva-los nesta tabela?Pensei em gerar e executor o script de todos os objetos do banco, mas isso dará um trabalho imenso.Estou pesquisando alguma SP que gera o DDL do objeto ai consigo fazer via T-SQL.Alguma dica?grato,GH

Responder
Nilton Cesar disse:

27 de maio de 2014 às 17:01

Solução simples e muito boa!!!
Show de bola!!

Responder
1. Fabricio Lima disse:
  
  28 de maio de 2014 às 22:43
  
  Obrigado Nilton.
  
  Responder
Roberto Pena disse:

2 de abril de 2015 às 00:07

Fabricio,

Muito bom artigo.
Sempre pensei em como versionar objetos do banco de dados, mas nunca havia encontrado.
Parabéns

Uma dúvida, ou desafio: Como faria para comparar versões para identificar o que foi alterado?

Abraços

Roberto Pena

Responder
1. Fabricio Lima disse:
  
  2 de abril de 2015 às 10:14
  
  Obrigado Roberto,
  
  Ai teria que utilizar uma ferramenta para isso.
  
  Da uma pesquisada que deve encontrar algumas. Não sei se serão pagas ou free.
  
  Abraços
  
  Responder
Leonardo Feitosa disse:

17 de agosto de 2015 às 21:50

Boa noite, Fabrício.

Parabéns pelo artigo, muito interessante e também muito útil.
Eu estava precisando de algo assim, pois sempre que recriava meus DBs e PROCS eu precisava ficar comparando as procs para saber se estavam subindo corretamente.

Show!

Parabéns novamente!!!

Responder
1. Fabricio Lima disse:
  
  18 de agosto de 2015 às 12:33
  
  Bacana Leonardo.
  
  Que bom que ajudou.
  
  Obrigado.
  
  Responder
Eduardo disse:

18 de março de 2021 às 12:14

Interessante essa abordagem!
Como integraria este controle ao Git / Github ?
Obrigado

Responder
1. Fabrício Lima disse:
  
  18 de março de 2021 às 12:16
  
  Nem ideia. Não sou o melhor cara de git. Uso o básico.
  
  Responder