SQL Server – Como criar um versionamento de código das suas Stored Procedures em HTML e com comentários da alteração

Dirceu Moraes Resende Filho

Bacharel em Sistemas de Informação pela Faculdade Metodista Granbery como aluno destaque, reconhecido pela Sociedade Brasileira da Computação, Pós-Graduando em Engenharia de Software na PUC Minas. Possui as certificações Microsoft MCP, MTA, MCSA e MCSE, além de ser reconhecido como Microsoft MVP Data Platform. Atualmente trabalha como Consultor Sênior SQL Server e BI na Fabrício Lima Soluções em BD, é um dos organizadores do SQL Server ES, grupo local que realiza eventos presenciais mensalmente na área de dados. Entusiasta de tecnologia, tem como foco principal SQL Server, MySQL, PowerBI, C#, PHP e JS em sua carreira de atuação.

SQL Server: como identificar e comprimir tabelas e índices sem compressão de dados

27 jan, 2022

Marketing e TI: descubra os benefícios de integrar essas áreas na sua empresa

18 nov, 2021

Azure SQL Database: STRING_SPLIT ganha parâmetro para incluir número das posições

Nesse artigo eu vou compartilhar com vocês como criar um versionamento de código das suas Stored Procedures em HTML e com comentários da alteração. No passado, eu já havia criado o post Como criar uma trigger de Auditoria para logar a manipulação de objetos no SQL Server, que mostra como gravar em uma tabela do banco, todas as alterações realizadas em um database, salvando informações sobre quem alterou, quando foi realizada a alteração, qual objeto alterado, e o script T-SQL do DDL/DCL utilizado para realizar essa alteração no banco.

Nesse post eu vou um pouco além disso, criando uma trigger que logue as alterações em uma tabela também, mas force a utilização de uma tag específica que informe o motivo da alteração nessa Stored Procedure (não permite alterar SP’s sem o motivo e nem utilizar o motivo anterior) e ao final da atualização, gera páginas HTML organizadas por objeto com toda a documentação desse objeto e o histórico das alterações.

Código-fonte dos objetos utilizados nesse artigo

Tabela utilizada

Log_Procedures

IF (OBJECT_ID('dbo.Log_Procedures') IS NULL)
BEGIN
    
    -- DROP TABLE dbo.Log_Procedures
    CREATE TABLE dbo.Log_Procedures (
        Id_Auditoria INT IDENTITY(1,1),
        Dt_Evento DATETIME NOT NULL,
        Nm_Procedure VARCHAR(100),
        Nm_Login VARCHAR(100),
        Ds_Procedure VARCHAR(MAX),
        Ds_Alt XML,
        Ds_Doc XML,
        Ds_Query XML
    )
        
    CREATE CLUSTERED INDEX SK01_Log_Procedures ON dbo.Log_Procedures(Id_Auditoria)
 
END

Funções utilizadas

fncDocumentacao_BuscaTag

CREATE FUNCTION [dbo].[fncDocumentacao_BuscaTag] (
    @Ds_Procedure VARCHAR(MAX), 
    @Nm_Tag VARCHAR(50), 
    @Nm_Wrap VARCHAR(50) = NULL
)
RETURNS XML
AS BEGIN

    DECLARE @Ds_Bloco VARCHAR(MAX) = ''

    DECLARE @Tag1 VARCHAR(MAX) = '<' + @Nm_Tag + '>'
    DECLARE @Tag2 VARCHAR(MAX) = '</' + @Nm_Tag + '>'

    DECLARE @achou INT = 1

    WHILE (@achou = 1) 
    BEGIN

        SET @achou = 0

        DECLARE @Pos1 INT, @Pos2 INT, @Pos3 INT
        
        SET @Pos1 = CHARINDEX(@Tag1, @Ds_Procedure)
        SET @Pos2 = CHARINDEX(@Tag2, @Ds_Procedure)
            
        IF (@Pos1 > 0 AND @Pos2 > 0) 
        BEGIN
            SET @Pos3 = @Pos2 - @Pos1 + LEN(@Tag2)
            SET @Ds_Bloco = @Ds_Bloco + SUBSTRING(@Ds_Procedure, @Pos1, @Pos3)
            SET @Ds_Procedure = SUBSTRING(@Ds_Procedure, @Pos2 + LEN(@Tag2), LEN(@Ds_Procedure))
            SET @achou = 1
        END
        
    END	

    IF (@Nm_Wrap IS NOT NULL AND @Ds_Bloco <> '') SET @Ds_Bloco = '<' + @Nm_Wrap + '>' + @Ds_Bloco + '</' + @Nm_Wrap + '>'

    DECLARE @Ds_Retorno XML
    SET @Ds_Retorno = @Ds_Bloco

    RETURN @Ds_Retorno

END
GO

Stored Procedures utilizadas

stpDOC_geraBloco_Versoes

CREATE PROCEDURE [dbo].[stpDOC_geraBloco_Versoes] (
@Nm_Procedure VARCHAR(MAX),
@Id_Log_Referencia INT,
@bloco VARCHAR(MAX) OUTPUT
) AS
BEGIN

SET @bloco = ‘
<div class=”divTitulo azul”>
<b>Histórico de Versões</b>
</div>

<table class=”tabResult” id=”tabVersoes” cellpadding=0 cellspacing=0 style=”width:100%”>
<thead>
<tr>
<td align=”center” style=”width:60px”>Versão</td>
<td align=”center” style=”width:160px”>Data</td>
<td style=”width:280px”>Autor</td>
<td align=”center” style=”width:60px”>Chamado</td>
<td>Motivo</td>
</tr>
</thead>
<tbody>’

IF (OBJECT_ID(‘TempDB..#Versoes’) IS NOT NULL) DROP TABLE #Versoes
SELECT *
INTO #Versoes
FROM dbo.Log_Procedures WITH(NOLOCK)
WHERE Nm_Procedure = @Nm_Procedure
ORDER BY Id_Auditoria DESC

WHILE EXISTS (SELECT * FROM #Versoes)
BEGIN

DECLARE @Id_Log INT, @Dt_Log DATETIME, @Nm_Login VARCHAR(MAX), @Ds_Alt XML, @Ds_Doc XML

SELECT TOP(1)
@Id_Log = Id_Auditoria,
@Dt_Log = Dt_Evento,
@Nm_Login = Nm_Login,
@Ds_Alt = Ds_Alt,
@Ds_Doc = Ds_Doc
FROM
#Versoes
ORDER BY
Id_Auditoria DESC

DECLARE @vAtual varchar(max)
SELECT @vAtual = COUNT(*) FROM #Versoes

DECLARE @Alt_Texto varchar(max), @Alt_Chamado varchar(max)
SET @Alt_Texto = IsNull(@Ds_Alt.value(‘(/alt/text())[1]’,’varchar(max)’),”)
SET @Alt_Chamado = IsNull(@Ds_Doc.value(‘(/doc/chamado)[1]’,’varchar(max)’),”)

DECLARE @corBG varchar(max) = ‘white’
IF (@Id_Log = @Id_Log_Referencia) SET @corBg = ‘lightgray’

SET @bloco = @bloco + ‘
<tr style=”background:’ + @corBG + ‘”>
<td align=”center”>’ + @vAtual + ‘</td>
<td align=”center”><a href=”./’ + CAST(@Id_Log as varchar(max))+’.html”>’ + CONVERT(VARCHAR(10), @Dt_Log, 103) + ‘ ‘ + CONVERT(VARCHAR(8), @Dt_Log, 114) + ‘</a></td>
<td>’ + @Nm_Login + ‘</td>
<td align=”center”>’ + @Alt_Chamado + ‘</td>
<td>’ + @Alt_Texto + ‘</td>
</tr>’

DELETE #Versoes WHERE Id_Auditoria = @Id_Log

END

SET @bloco = @bloco+ ‘
</tbody>
</table>’

END

stpEscreve_Arquivo_FSO

CREATE PROCEDURE [dbo].[stpEscreve_Arquivo_FSO] (
    @String VARCHAR(MAX),
    @Ds_Arquivo VARCHAR(1501)
)
AS
BEGIN

    DECLARE
        @objFileSystem INT,
        @objTextStream INT,
        @objErrorObject INT,
        @strErrorMessage VARCHAR(1000),
        @Command VARCHAR(1000),
        @hr INT

    SET NOCOUNT ON

    SELECT
        @strErrorMessage = 'opening the File System Object'
    
    EXECUTE @hr = sp_OACreate
        'Scripting.FileSystemObject',
        @objFileSystem OUT

    
    IF @HR = 0
        SELECT
            @objErrorObject = @objFileSystem,
            @strErrorMessage = 'Creating file "' + @Ds_Arquivo + '"'
    
    
    IF @HR = 0
        EXECUTE @hr = sp_OAMethod
            @objFileSystem,
            'CreateTextFile',
            @objTextStream OUT,
            @Ds_Arquivo,
            2,
            True

    IF @HR = 0
        SELECT
            @objErrorObject = @objTextStream,
            @strErrorMessage = 'writing to the file "' + @Ds_Arquivo + '"'
    
    
    IF @HR = 0
        EXECUTE @hr = sp_OAMethod
            @objTextStream,
            'Write',
            NULL,
            @String

    
    IF @HR = 0
        SELECT
            @objErrorObject = @objTextStream,
            @strErrorMessage = 'closing the file "' + @Ds_Arquivo + '"'
    
    
    IF @HR = 0
        EXECUTE @hr = sp_OAMethod
            @objTextStream,
            'Close'

    
    IF @hr <> 0
    BEGIN
    
        DECLARE
            @Source VARCHAR(255),
            @Description VARCHAR(255),
            @Helpfile VARCHAR(255),
            @HelpID INT
    
        EXECUTE sp_OAGetErrorInfo
            @objErrorObject,
            @source OUTPUT,
            @Description OUTPUT,
            @Helpfile OUTPUT,
            @HelpID OUTPUT
        
        
        SELECT
            @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something') + ', ' + COALESCE(@Description, '')
        
        
        RAISERROR (@strErrorMessage,16,1)
        
    END
    
    
    EXECUTE sp_OADestroy
        @objTextStream
    
    EXECUTE sp_OADestroy
        @objTextStream
        
END

stpDOC_Gera_Arquivos

CREATE PROCEDURE [dbo].[stpDOC_Gera_Arquivos] (
    @Ds_Caminho VARCHAR(500),
    @Fl_Todos INT = 0
)
AS 
BEGIN


    SET NOCOUNT ON


    IF (OBJECT_ID('TempDB..#Procedures') IS NOT NULL) DROP TABLE #Procedures
    SELECT Nm_Procedure, Id_Auditoria
    INTO #Procedures
    FROM dbo.Log_Procedures WITH(NOLOCK)

    IF (OBJECT_ID('TempDB..#Ultimo_Log') IS NOT NULL) DROP TABLE #Ultimo_Log
    SELECT Nm_Procedure, MAX(Id_Auditoria) AS Id_Auditoria
    INTO #Ultimo_Log 
    FROM #Procedures 
    GROUP BY Nm_Procedure

    IF (@Fl_Todos = 0) DELETE #Procedures WHERE Id_Auditoria NOT IN (SELECT Id_Auditoria FROM #Ultimo_Log)

    DECLARE @Html_index varchar(max) = '
    <!DOCTYPE html>
    <html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Documentação</title>
    </head>

    <body style="font-family:Verdana; font-size:14px">
        <h2>Versionamento de Stored Procedures</h2>
        <ul>
    '


    DECLARE @Nm_Arquivo VARCHAR(100)

    WHILE EXISTS (SELECT * FROM #Procedures) 
    BEGIN
        
        DECLARE @Nm_Procedure varchar(max), @Id_Auditoria int, @Id_Max int
        SELECT TOP(1) @Nm_Procedure = Nm_Procedure, @Id_Auditoria = Id_Auditoria FROM #Procedures A 
        SELECT @Id_Max = Id_Auditoria FROM #Ultimo_Log WHERE Nm_Procedure = @Nm_Procedure
        
        DECLARE @Nm_Login varchar(max), @Ds_Procedure varchar(max), @Dt_Log datetime, @Ds_Alt xml, @Ds_Doc xml, @Ds_Steps xml	
        
        SELECT 
            @Nm_Login = Nm_Login,
            @Ds_Procedure = Ds_Procedure,
            @Dt_Log = Dt_Evento,
            @Ds_Alt = Ds_Alt,
            @Ds_Doc = Ds_Doc
        FROM 
            dbo.Log_Procedures A WITH(NOLOCK)
        WHERE 
            Nm_Procedure = @Nm_Procedure 
            AND Id_Auditoria = @Id_Auditoria
        
        
        DECLARE @Doc_Titulo varchar(max), @Doc_Descricao varchar(max)
        SET @Doc_Titulo = IsNull(@Ds_Doc.value('(/doc/titulo)[1]','varchar(max)'),@Nm_Procedure)
        SET @Doc_Descricao = IsNull(@Ds_Doc.value('(/doc/descricao)[1]','varchar(max)'),'<red>/doc/descricao em branco ou não definido.</red>')
        
        DECLARE @bloco_Versoes varchar(max) = ''
        EXEC dbo.stpDOC_geraBloco_Versoes @Nm_Procedure, @Id_Auditoria, @bloco_Versoes OUT
    
        DECLARE @Html varchar(max)
        SET @Html = 
        '
        <!DOCTYPE html>
        <html>

            <head>
                <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
                <title>Documentação</title>

                <style type="text/css">
                    .tabResult thead {
                        background: #03a9f4;
                        color: #fff;
                        font-weight: bold;
                        text-align: center;
                        line-height: 28px;
                    }

                    .tabResult td {
                        padding: 5px;
                        border: 1px solid #eaeaea;
                    }

                    .tabResult tr:hover {
                        background: #539eb5 !important;
                    }

                </style>

            </head>

            <body style="font-family:Verdana; font-size:14px">

                <a href="../index.html">Voltar à Home</a>
                <br/>
                <br/>

                <div style="margin-bottom:10px">
                    <div class="header grad_preto">
                        <table cellpadding=0 cellspacing=0 style="width:100%">
                            <tr>
                                <td><b>' + @Doc_Titulo + '</b></td>
                                <td align="right"><b><a href="../index.html" style="text-decoration:none; color:white">Voltar</a></b></td>
                            </tr>
                        </table>
                    </div>	
                    <div style="padding:5px; margin-top:10px">' + @Doc_Descricao + '</div>
                </div>
            
                <br/>
                
                ' + @bloco_Versoes + '
            
                <br/>
            
                <div class="divTitulo verde">
                    <b>Código Fonte</b>
                </div>
            
                <pre id="preQuery" class="sh_sql"><xmp>' + @Ds_Procedure + '</xmp></pre>

            </body>

        </html>'
        
        
        DECLARE @Ds_Caminho_Arquivo VARCHAR(500) = @Ds_Caminho + @Nm_Procedure + '\'
        EXEC master.dbo.xp_create_subdir @Ds_Caminho_Arquivo
        
        SET @Nm_Arquivo = @Ds_Caminho_Arquivo + CAST(@Id_Auditoria AS VARCHAR(MAX)) + '.html'
    
        EXEC dbo.stpEscreve_Arquivo_FSO @Html, @Nm_Arquivo

        IF (@Id_Auditoria = @Id_Max) 
        BEGIN
            
            SET @Nm_Arquivo = @Ds_Caminho_Arquivo + 'index.html'
            EXEC dbo.stpEscreve_Arquivo_FSO @Html, @Nm_Arquivo
            
            SET @Html_index = @Html_index + '
                <div style="padding:5px">
                    <li><a href="' + @Ds_Caminho_Arquivo + 'index.html" style="text-decoration:none; color:black">' + @Nm_Procedure + '</a></li>
                </div>
            '	
        END

        DELETE #Procedures WHERE Id_Auditoria = @Id_Auditoria

    END


    SET @Html_index = @Html_index+'
            </ul>

        </body>
    </html>
    '

    SET @Nm_Arquivo = @Ds_Caminho + 'index.html'
    EXEC dbo.stpEscreve_Arquivo_FSO @Html_index, @Nm_Arquivo
    
END

Trigger utilizada

trgDDLAuditQuery

CREATE TRIGGER [trgDDLAuditQuery]
ON DATABASE 
FOR ALTER_PROCEDURE
AS
BEGIN

    SET NOCOUNT ON
   
    DECLARE @EventAtual XML = EVENTDATA()    
    DECLARE @Nm_Procedure VARCHAR(MAX) = @EventAtual.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)')
    DECLARE @QueryAtual VARCHAR(MAX) = @EventAtual.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
    
    DECLARE @Nm_Login VARCHAR(MAX) = @EventAtual.value('(/EVENT_INSTANCE/LoginName/text())[1]','varchar(50)')
    
    DECLARE @altAtual XML = dbo.fncDocumentacao_BuscaTag(@QueryAtual,'alt',NULL)
    DECLARE @docAtual XML = dbo.fncDocumentacao_BuscaTag(@QueryAtual,'doc',NULL)
    
    DECLARE @Max_Id_Alteracao INT
    SELECT @Max_Id_Alteracao = MAX(Id_Auditoria) FROM dbo.Log_Procedures WHERE Nm_Procedure = @Nm_Procedure
    
    DECLARE @EventAnterior XML = (SELECT TOP(1) Ds_Query FROM dbo.Log_Procedures WHERE Id_Auditoria = @Max_Id_Alteracao ORDER BY Id_Auditoria DESC)
    DECLARE @QueryAnterior VARCHAR(MAX) = @EventAnterior.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')    
    DECLARE @altAnterior XML = dbo.fncDocumentacao_BuscaTag(@QueryAnterior,'alt',NULL)
      
    DECLARE @dsAtual VARCHAR(MAX) = @altAtual.value('(/alt)[1]', 'nvarchar(max)')
    DECLARE @dsAnterior VARCHAR(MAX) = @altAnterior.value('(/alt)[1]', 'nvarchar(max)')
      
    IF (ISNULL(@dsAtual,'') = ISNULL(@dsAnterior,'') OR ISNULL(@dsAtual, '') = '')
    BEGIN
        ROLLBACK
        RAISERROR('O motivo de alteração não foi informado. FAVOR INFORMAR O MOTIVO DA ALTERAÇÃO USANDO A TAG <alt> como comentário (/* <alt>Descrição da alteração</alt> */).', 16, 1)
        RETURN
    END
    
    
    INSERT INTO dbo.Log_Procedures (Dt_Evento, Nm_Procedure, Nm_Login, Ds_Procedure, Ds_Alt, Ds_Doc, Ds_Query)
    SELECT GETDATE(), @Nm_Procedure, @Nm_Login, @QueryAtual, @altAtual, @docAtual, @EventAtual


    EXEC dbo.stpDOC_Gera_Arquivos
        @Ds_Caminho = 'C:\Documentação\' -- varchar(500)
    


END
GO

ENABLE TRIGGER [trgDDLAuditQuery] ON DATABASE
GO

Lembrando que como utilizo o recurso de OLE Automation na Stored Procedure stpEscreve_Arquivo_FSO para gravar os arquivos no disco, vamos precisar habilitar esse recurso na instância:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

Como versionar os códigos

Após criar todos os objetos necessários para esse controle, você verá que não vai conseguir mais alterar uma SP sem informar o motivo da alteração:

Forma simples para documentar as alterações:

Forma completa para documentar as alterações:

/* 

    <alt>Inclusão do select</alt>
    <doc>
        <titulo>Inclusão de um select na sys.objects</titulo>
        <descricao>Alteração realizada para incluir um select * from no final da SP</descricao>
        <chamado>1234</chamado>
    </doc>

*/

ALTER PROC dbo.stpTeste
AS
SELECT * FROM sys.objects

Alguns exemplos do resultado final

Página inicial

Visualizando a versão 2

Visualizando a versão 1

É isso aí, pessoal!
Espero que tenham gostado desse post e até a próxima!

Artigo original

De 0 a 10, o quanto você recomendaria este artigo para um amigo?