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

Logo
em seguida, criaremos a trigger em 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.

CREATETRIGGER trgTrace_Alteracao_Objeto
ONDATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN

Vamos aos testes. Execute os procedimentos abaixo para verificar se eles 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
CREATEFUNCTION dbo.fncControle_versao() RETURNSVARCHAR(10) AS BEGINRETURN'Versão 1.0'END
GO
ALTERFUNCTION dbo.fncControle_versao() RETURNSVARCHAR(10) AS BEGINRETURN'Versão 2.0'END
GO
DROPFUNCTION 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, a 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 *

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>
<SetOptionsANSI_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, veja a planilha que há neste artigo.
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.