SQL Server - How to create code versioning of your Stored Procedures in HTML and with change comments

Views: 2.321 views

In this article

Reading Time: 7 minutes

Speak guys!

In this article I will share with you how to create a code version of your Stored Procedures in HTML and with comments on the change. In the past, I had already created the post How to create an Audit trigger to log object manipulation in SQL Server, which shows how to write to a database table all changes made to a database, saving information about who changed, when the change was made, which object was changed, and the DDL / DCL T-SQL script used to make that change. in the bank.

In this post I go a little further, creating a trigger that logs the changes in a table too, but force the use of a specific tag that informs the reason for the change in this Stored Procedure (does not allow changing SP's without the reason and neither use the reason) and at the end of the update, generates HTML pages organized by object with all the documentation for that object and the history of changes.

Source code for objects used in this article

View information

Used table

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

Functions Used

fncDocumentacao_SearchTag

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 Used

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

stpWrite_File_File

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_Archives

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

Used trigger

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

Remembering that as I use the OLE Automation feature in the Stored Procedure stpWrite_File_OF to write the files to disk, we will need enable this feature on instance:

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;

How to version codes

Once you have created all the objects required for this control, you will find that you will no longer be able to change an SP without stating the reason for the change:

Simple way to document changes:

Complete way to document changes:

/* 

    <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

Some examples of the final result

Homepage

Viewing the 2 Version

Viewing the 1 Version

That's it folks!
I hope you enjoyed this post and see you next time!

Henrique Almeida said:

25 May 2022 to 10: 06

Very good dirceu, I really liked this tip.
About versioning, do you recommend using liquibase?
Thanks in advance.

Mariana Del Nero said:

15 February 2019 to 13: 58

Thanks Dirceu for always sharing very relevant content with a quick and easy read.

Leonardo Vicentini Fiorot said:

20 from 2018 to 15 at 59: XNUMX

great!

Under development

November 17th, 2020

[…] Original article […]

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

SQL Server - How to create a code version of your Stored Procedures in HTML and with comments on the change

Source code for objects used in this article

Used table

Functions Used

Stored Procedures Used

Used trigger

How to version codes

Some examples of the final result

You may also like ...

Comments

Leave a Comment Cancel reply

Source code for objects used in this article

Used table

Functions Used

Stored Procedures Used

Used trigger

How to version codes

Some examples of the final result

You may also like ...

SQL Server - Permissions for Using OLE Automation (Yes, Documentation is Wrong)

SQL Server - Using CROSS JOIN (Cartesian product) to create rows in the query result

Introduction to SQL Common Language Runtime (CLR) in SQL Server

Comments

Leave a Comment Cancel reply