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

Post Views 2,465 views

In this article

Reading time 7 minutes

Hey guys!

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 How to create an Audit trigger to log object manipulation in 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

Visualizar informações

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

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

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

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

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

CREATE PROCEDURE [dbo].[stpDOC_geraBloco_Versoes] (

@Nm_Procedure VARCHAR(MAX),

@Id_Log_Referencia INT,

@bloco VARCHAR(MAX) OUTPUT

) AS

BEGIN

SET @bloco = '

<b>Histórico de Versões</b>

</div>

<thead>

<tr>

<td align="center" style="width:60px">Versão</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 + '

<td align="center">' + @vAtual + '</td>

<td align="center"><a + 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

CREATE PROCEDURE [dbo].[stpEscreve_Arquivo_FSO] (

@String VARCHAR(MAX),

@Ds_Arquivo VARCHAR(1501)

)

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,

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

CREATE PROCEDURE [dbo].[stpDOC_Gera_Arquivos] (

@Ds_Caminho VARCHAR(500),

@Fl_Todos INT = 0

)

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>

<title>Documentação</title>

</head>

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

<title>Documentação</title>

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

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

<br/>

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

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

<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

CREATE TRIGGER [trgDDLAuditQuery]

ON DATABASE

FOR ALTER_PROCEDURE

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

ENABLE TRIGGER [trgDDLAuditQuery] ON DATABASE

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

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure 'Ole Automation Procedures', 1;

RECONFIGURE;

sp_configure 'Agent XPs', 1;

RECONFIGURE;

sp_configure 'show advanced options', 0;

RECONFIGURE;

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

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

</doc>

ALTER PROC dbo.stpTeste

SELECT * FROM sys.objects

Alguns exemplos do resultado final

Página inicial

Visualizando a versão 2

Visualizando a versão 1

And that's it, folks!
Espero que tenham gostado desse post e até a próxima!

Henrique Almeida disse:

May 25, 2022 às 10:06

Muito bom dirceu, gostei bastante dessa dica.
Sobre versionamento, você indica usar o liquibase?
Obrigado desde já.

Responder
Mariana Del Nero disse:

February 15, 2019 às 13:58

Obrigada Dirceu por sempre compartilhar conteúdo de muita relevância com uma leitura rápida e fácil.

Responder
Leonardo Vicentini Fiorot disse:

August 20, 2018 às 15:59

muito show!

Responder

Em desenvolvimento

November 17, 2020

[…] Artigo original […]

Subscribe to blog by email

Blog Views

Post Archive

Categories

Recent posts

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

Código-fonte dos objetos utilizados nesse artigo

Tabela utilizada

Funções utilizadas

Stored Procedures utilizadas

Trigger utilizada

Como versionar os códigos

Alguns exemplos do resultado final

You may also like...

4 Responses

Deixe uma respostaCancelar resposta

Código-fonte dos objetos utilizados nesse artigo

Tabela utilizada

Funções utilizadas

Stored Procedures utilizadas

Trigger utilizada

Como versionar os códigos

Alguns exemplos do resultado final

You may also like...

SQL Server – Como identificar o tipo de dado de colunas e parâmetros de Tabelas, Views, Stored Procedures e Funções

SQL Server – Como exportar e importar arquivos com dados tabulares (Ex: CSV) utilizando o CLR (C#)

Como identificar a porta utilizada pela instância do SQL Server

4 Responses

Deixe uma respostaCancelar resposta