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

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.

por Fabrício França Lima

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 Fabricio, 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](20) 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

    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

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

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

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"&gt;" e"&lt;"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:

1 - Copie o texto entre as tags <CommandText> </CommandText>
2 - Abra uma nova query e cole o texto.
3 - Teche Ctrl+H e realize o replace de"&gt;"por">"de todo o texto.
4 - Teche Ctrl+H e realize o replace de"&lt;"por"<"de todo o texto.
5 – 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 artigoCriando 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.

Abraços,

Fabrício França Lima

MCITP - Database Administrator

http://fabriciodba.spaces.live.com/

http://twitter.com/fabriciodba