{"id":286,"date":"2010-04-11T15:18:01","date_gmt":"2010-04-11T18:18:01","guid":{"rendered":"http:\/\/fabriciodba.wordpress.com\/2010\/04\/11\/como-criar-um-controle-de-versao-de-procedures-views-e-functions-no-sql-server"},"modified":"2016-12-16T13:32:14","modified_gmt":"2016-12-16T15:32:14","slug":"como-criar-um-controle-de-versao-de-procedures-views-e-functions-no-sql-server","status":"publish","type":"post","link":"https:\/\/fabriciolima.net\/blog\/2010\/04\/11\/como-criar-um-controle-de-versao-de-procedures-views-e-functions-no-sql-server\/","title":{"rendered":"Como criar um Controle de Vers\u00e3o de Procedures, Views e Functions no SQL Server"},"content":{"rendered":"
\n

Quantas vezes j\u00e1 te perguntaram qual era o c\u00f3digo de uma fun\u00e7\u00e3o, procedure ou view em uma determinada data?<\/p>\n

At\u00e9 o SQL Server 2000, s\u00f3 era poss\u00edvel obter essa informa\u00e7\u00e3o atrav\u00e9s da gera\u00e7\u00e3o peri\u00f3dica 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\u00e3o de objetos do banco de dados, tais como, fun\u00e7\u00f5es, procedures, views, tabelas, \u00edndices e etc. O procedimento \u00e9 muito simples de se implementar e \u00e9 de muita import\u00e2ncia para um ambiente de banco de dados. Portanto, se voc\u00ea ainda n\u00e3o guarda essas informa\u00e7\u00f5es, aconselho que implemente esse Controle de Vers\u00e3o de objetos assim que poss\u00edvel em seu ambiente.<\/p>\n

Inicialmente, devemos gerar um script de nossa database para armazenar as informa\u00e7\u00f5es dos objetos da forma como eles est\u00e3o nesse momento. Para fazer isso, basta ir no Object Explore do Management Studio, clicar com o bot\u00e3o direito sobre uma database -> Tasks – > Generate Scripts , depois \u00e9 s\u00f3 seguir o Wizard marcando a op\u00e7\u00e3o de gerar o script para todos os objetos dessa database e salvar o script em um arquivo.<\/p>\n

Esse procedimento deve ser realizado para todas as databases que voc\u00ea queira guardar as informa\u00e7\u00f5es.<\/p>\n

Nesse momento, temos a vers\u00e3o de todos os objetos das databases de nosso banco de dados. Para guardar as altera\u00e7\u00f5es futuras criaremos uma tabela e uma trigger DDL que ir\u00e1 inserir registros em nossa tabela com as novas vers\u00f5es dos objetos.<\/p>\n

Mas Fabr\u00edcio, e as altera\u00e7\u00f5es que j\u00e1 foram realizadas, eu n\u00e3o consigo descobrir?
\nInfelizmente n\u00e3o. O controle de vers\u00e3o passar\u00e1 a valer somente a partir de sua implementa\u00e7\u00e3o, s\u00f3 a partir desse momento que saberemos como um objeto estava em qualquer dia e hora que precisarmos.<\/p>\n

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

\r\nCREATE TABLE [FabricioLima].[dbo].[Trace_Alteracao_Objeto] (\r\n\t[Id_Trace_Alteracao_Objeto] [INT] IDENTITY(1,1) NOT NULL,\r\n\t[Tp_Evento] [VARCHAR](30) NULL,\r\n\t[Dt_Alteracao] [DATETIME] NULL,\r\n\t[Nm_Servidor] [VARCHAR](100) NULL,\r\n\t[Nm_Login] [VARCHAR](50) NULL,\r\n\t[Nm_Database] [VARCHAR](20) NULL,\r\n\t[Nm_Objeto] [VARCHAR](50) NULL,\r\n\t[Ds_Evento] [XML] NULL\r\n) ON [PRIMARY]\r\n<\/pre>\n
Logo em seguida, criaremos a trigger a n\u00edvel de database que enviar\u00e1 os dados para a nossa tabela. Essa trigger deve ser criada em todas as databases que voc\u00ea queira fazer o controle de vers\u00e3o dos objetos.<\/p>\n
\r\nCREATE TRIGGER [trgTrace_Alteracao_Objeto]\r\nON DATABASE\r\nFOR DDL_DATABASE_LEVEL_EVENTS\r\nAS\r\nBEGIN\r\n    SET NOCOUNT ON\r\n    SET ARITHABORT ON\r\n\r\n    DECLARE @Evento XML\r\n    SET @Evento = EVENTDATA()\r\n    \r\n    INSERT INTO [FabricioLima].[dbo].[Trace_Alteracao_Objeto] ( [Tp_Evento], [Dt_Alteracao], [Nm_Servidor], [Nm_Login], [Nm_Database], [Nm_Objeto], [Ds_Evento] )\r\n    SELECT  @Evento.value('(\/EVENT_INSTANCE\/EventType\/text())[1]','VARCHAR(50)') [Tipo_Evento],\r\n            @Evento.value('(\/EVENT_INSTANCE\/PostTime\/text())[1]','DATETIME') [PostTime],\r\n            @Evento.value('(\/EVENT_INSTANCE\/ServerName\/text())[1]','VARCHAR(50)') [ServerName],\r\n            @Evento.value('(\/EVENT_INSTANCE\/LoginName\/text())[1]','VARCHAR(50)') [LoginName],\r\n            @Evento.value('(\/EVENT_INSTANCE\/DatabaseName\/text())[1]','VARCHAR(50)') [DatabaseName],\r\n            @Evento.value('(\/EVENT_INSTANCE\/ObjectName\/text())[1]','VARCHAR(50)') [ObjectName], \r\n            @Evento\r\nEND\r\n<\/pre>\n
Vamos aos testes. Execute os procedimentos abaixo para verificar se os mesmos ser\u00e3o registrados.<\/p>\n
\r\nCREATE PROCEDURE [dbo].[stpControle_versao] AS SELECT 'Vers\u00e3o 1.0'\r\n\r\nGO\r\nALTER PROCEDURE [dbo].[stpControle_versao] AS SELECT 'Vers\u00e3o 2.0'\r\n\r\nGO\r\nDROP PROCEDURE [dbo].[stpControle_versao]\r\n\r\nGO\r\nCREATE FUNCTION [dbo].[fncControle_versao]() RETURNS VARCHAR(10) AS  BEGIN RETURN 'Vers\u00e3o 1.0' END\r\n\r\nGO\r\nALTER FUNCTION [dbo].[fncControle_versao]() RETURNS VARCHAR(10) AS  BEGIN RETURN 'Vers\u00e3o 2.0' END\r\n\r\nGO\r\nDROP FUNCTION [dbo].[fncControle_versao]\r\n<\/pre>\n
Para conferir as informa\u00e7\u00f5es do controle de vers\u00e3o, basta executar a query abaixo.<\/p>\n
\r\nSELECT [Tp_Evento], [Dt_Alteracao], [Nm_Servidor], [Nm_Login], [Nm_Database], [Nm_Objeto], [Ds_Evento]\r\nFROM [FabricioLima].[dbo].[Trace_Alteracao_Objeto] WITH(NOLOCK)\r\nORDER BY [Dt_Alteracao]\r\n<\/pre>\n
Essa query retorna o seguinte resultado.<\/p>\n
\"\"<\/p>\n<\/div>\n
Podemos verificar que a cria\u00e7\u00e3o, altera\u00e7\u00e3o e a exclus\u00e3o dos dois objetos foram registradas com a data da altera\u00e7\u00e3o e o Login de quem alterou.<\/p>\n
Segue um exemplo da utiliza\u00e7\u00e3o do controle de vers\u00e3o:<\/p>\n
Imagine que foi gerado um script de uma database chamada FabricioLima no dia 01\/01\/2010.
\nEssa database possui uma SP chamada stpControle_Versao, onde a mesma foi alterada nos dias 01\/02\/2010, 01\/03\/2010 e 01\/04\/2010.
\nNos dias em que o objeto foi alterado, a trigger inseriu uma linha na tabela de trace com as altera\u00e7\u00f5es.<\/p>\n
Abaixo mostro onde encontrar a vers\u00e3o procurada de acordo com uma determinada necessidade:<\/p>\n
Preciso de saber como a SP estava no dia 10\/01\/2010: Est\u00e1 no Script gerado dia 01\/01\/2010
\nPreciso de saber como a SP estava no dia 30\/01\/2010: Est\u00e1 no Script gerado dia 01\/01\/2010
\nPreciso de saber como a SP estava no dia 20\/02\/2010: Est\u00e1 na tabela Trace_Alteracao_Objeto com Dt_Alteracao = 01\/02\/2010
\nPreciso de saber como a SP estava no dia 20\/03\/2010: Est\u00e1 na tabela Trace_Alteracao_Objeto com Dt_Alteracao = 01\/03\/2010
\nPreciso de saber como a SP estava no dia 05\/04\/2010: Como a \u00faltima altera\u00e7\u00e3o foi no dia 01\/04\/2010, o c\u00f3digo da SP no dia 05\/04\/2010 \u00e9 o c\u00f3digo atual da SP.<\/p>\n
Assim, quando algu\u00e9m te solicitar o script de um objeto, basta executar a query abaixo para recuperar todas as altera\u00e7\u00f5es nesse objeto.<\/p>\n
\r\nSELECT *\r\nFROM [dbo].[Trace_Alteracao_Objeto] WITH(NOLOCK)\r\nWHERE [Nm_Objeto] = 'Nome_Objeto'\r\nORDER BY [Dt_Alteracao]\r\n<\/pre>\n
Caso nenhum resultado seja retornado, esse objeto nunca foi alterado desde a implementa\u00e7\u00e3o do Controle de Vers\u00e3o.<\/p>\n
Depois de encontrada a vers\u00e3o desejada na tabela, para visualizar seu c\u00f3digo basta clicar sobre a coluna Ds_Evento dessa linha. Uma nova janela \u00e9 aberta com o c\u00f3digo XML do evento que foi executado, conforme podemos ver abaixo.<\/p>\n
\r\n<EVENT_INSTANCE>\r\n  <EventType>ALTER_PROCEDURE<\/EventType>\r\n  <PostTime>2010-04-11T10:51:44.483<\/PostTime>\r\n  <SPID>51<\/SPID>\r\n  <ServerName>FABRICIO<\/ServerName>\r\n  <LoginName>FABRICIO\\Fabriciol1<\/LoginName>\r\n  <UserName>dbo<\/UserName>\r\n  <DatabaseName>FabricioLima<\/DatabaseName>\r\n  <SchemaName>dbo<\/SchemaName>\r\n  <ObjectName>stpControle_versao<\/ObjectName>\r\n  <ObjectType>PROCEDURE<\/ObjectType>\r\n  <TSQLCommand>\r\n    <SetOptions ANSI_NULLS=\"ON\" ANSI_NULL_DEFAULT=\"ON\" ANSI_PADDING=\"ON\" QUOTED_IDENTIFIER=\"ON\" ENCRYPTED=\"FALSE\" \/>\r\n    <CommandText>ALTER PROCEDURE stpControle_versao AS SELECT 'Vers\u00e3o 2.0'\r\n    <\/CommandText>\r\n  <\/TSQLCommand>\r\n<\/EVENT_INSTANCE>\r\n<\/pre>\n
O c\u00f3digo do objeto fica entre as tags <CommandText> e <\/CommandText> na mesma formata\u00e7\u00e3o utilizada na cria\u00e7\u00e3o ou altera\u00e7\u00e3o do objeto.<\/p>\n
Caso os objetos possuam os caracteres ‘>’<\/span> e ‘<‘<\/span> em seu script, eles ser\u00e3o substitu\u00eddos no campo XML pelas strings ‘&gt;’<\/span> e ‘&lt;’<\/span> respectivamente. Logo, caso voc\u00ea precise subir uma vers\u00e3o antiga de um objeto que possua esses caracteres, deve ser realizado um replace no script do objeto como abaixo:<\/p>\n
    \n
  1.  Copie o texto entre as tags <CommandText> <\/CommandText>\n
  2.  Abra uma nova query e cole o texto.\n
  3.  Tecle Ctrl+H e realize o replace de ‘&gt;’<\/span> por ‘>’<\/span> de todo o texto.\n
  4.  Tecle Ctrl+H e realize o replace de ‘&lt;’<\/span> por ‘<‘<\/span> de todo o texto.\n
  5.  Tecle F5 para subir o objeto.\n<\/ol>\n
    Agora que as informa\u00e7\u00f5es de cria\u00e7\u00e3o, altera\u00e7\u00e3o e exclus\u00e3o de objetos est\u00e3o armazenadas em seu banco de dados, \u00e9 poss\u00edvel adicionar mais uma informa\u00e7\u00e3o a planilha do meu primeiro artigo Criando um CheckList Autom\u00e1tico do Banco de Dados<\/a>. No meu ambiente existe uma aba dessa planilha com as altera\u00e7\u00f5es de objetos realizadas no dia anterior, assim, acompanho diariamente todos os objetos que s\u00e3o alterados no meu Banco de Dados.<\/p>\n
    Desde quando me mostraram essa nova funcionalidade do SQL Server 2005 eu implementei esse Controle de Vers\u00e3o dos objetos no meu banco de dados. At\u00e9 hoje, j\u00e1 perdi as contas do n\u00famero de vezes que me solicitaram essa informa\u00e7\u00e3o, al\u00e9m de identificar os culpados de alterar um objeto de forma err\u00f4nea.<\/p>\n
    Espero ter ajudado.<\/p>\n
     
    \nGostou desse Post?<\/b><\/p>\n
    Cadastre seu e-mail para receber novos Posts e curta minha P\u00e1gina no Facebook<\/a> para receber Dicas de Leituras e Eventos sobre SQL Server.<\/p>\n
    Abra\u00e7os,<\/p>\n
    Fabr\u00edcio Lima<\/p>\n
    MCITP \u2013 Database Administrator<\/p>\n
    Consultor e Instrutor SQL Server<\/p>\n
    Trabalha com SQL Server desde 2006<\/p>\n","protected":false},"excerpt":{"rendered":"
    Quantas vezes j\u00e1 te perguntaram qual era o c\u00f3digo de uma fun\u00e7\u00e3o, procedure ou view em uma determinada data? At\u00e9 o SQL Server 2000, s\u00f3 era poss\u00edvel obter essa informa\u00e7\u00e3o atrav\u00e9s da gera\u00e7\u00e3o peri\u00f3dica de arquivos com scripts dos objetos do banco de dados. Entretanto, a partir do SQL Server 2005, esse procedimento melhorou com […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[3,280],"tags":[14,52,18,19,23,26,48,33,50,34,35],"class_list":["post-286","post","type-post","status-publish","format-standard","hentry","category-administracao-de-bd","category-virtual-pass-br","tag-auditoria","tag-auditoria-de-sistemas","tag-checklist","tag-controle-de-versao","tag-dba","tag-excel","tag-planilha","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-sql-server-2008-r2"],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/286","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/comments?post=286"}],"version-history":[{"count":0,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/286\/revisions"}],"wp:attachment":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/media?parent=286"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/categories?post=286"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/tags?post=286"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}