Olá pessoal!
Bom dia.

Introdução

Neste post, vou mostrar a vocês como gerar os scripts de criação de objetos de programação do SQL Server (Functions, Trigger, Stored Procedures e Views) e exportá-los para arquivos .SQL utilizando consultas a views de catálogo. Desta forma, podemos automatizar a geração desses scripts.

Uma outra necessidade que me levou a criar esse script, foi que no meu database de funções úteis, tenho centenas de SP’s e funções CLR e T-SQL juntos. Às vezes quando quero copiar as funções e Stored Procedures escritas em T-SQL, ignorando as CLR, não tenho uma forma simples de fazer isso, pois a interface do SQL Server Management Studio não permite filtrar funções e stored procedures T-SQL e CLR, gerando o script de todas elas e depois preciso excluir os scripts que são CLR.

SQL Server - Tasks - Generate and Publish Scripts
SQL Server - Tasks - Generate and Publish Scripts

Consultando o código-fonte dos objetos

Realizando uma simples consulta nas views de catálogo sys.sql_modules e sys.objects, podemos obter o código-fonte dos objetos de programação do SQL Server no database atual:

SELECT 
    B.name AS Ds_Objeto,
    B.[type_desc] AS Ds_Tipo,
    A.[definition] AS Ds_Comando
FROM 
    sys.sql_modules    A   WITH(NOLOCK)
    JOIN sys.objects   B   WITH(NOLOCK)    ON  A.[object_id] = B.[object_id]

SQL Server - Query to Generate and Publish Scripts
SQL Server - Query to Generate and Publish Scripts

Como exportar os objetos de programação para arquivos .SQL

Uma vez que já temos o código-fonte de cada objeto, basta agora exportá-lo para arquivos de texto e nossa tarefa está concluída. Visando facilitar esse processo, vou disponibilizar para vocês, uma Stored Procedure que já faz essa tarefa, iterando entre cada objeto e gerando o script dos objetos.

Como pré-requisito para que ela funciona, vocês precisarão dos objetos abaixo, cujo código-fonte está disponível no meu post SQL Server – Como listar, ler, escrever, copiar, excluir e mover arquivos com o CLR (C#):
– fncArquivo_Existe
– stpApaga_Arquivo
– stpEscreve_Arquivo

CREATE PROCEDURE dbo.stpExporta_Objetos_SQL ( 
    @Ds_Database SYSNAME,
    @Diretorio_Destino VARCHAR(600),
    @Fl_Arquivo_Unico BIT = 0,
    @Fl_Gera_Com_Create BIT = 1,
    @Fl_Exporta_Procedures BIT = 0,
    @Fl_Exporta_Functions BIT = 0,
    @Fl_Exporta_Triggers BIT = 0,
    @Fl_Exporta_Views BIT = 0
)
AS BEGIN


    ------------------------------------------------------------------
    -- RECUPERA OS OBJETOS
    ------------------------------------------------------------------

    DECLARE 
        @Query VARCHAR(MAX),
        @Filtro_Tipos VARCHAR(MAX) = '''X'''


    IF (@Fl_Exporta_Procedures = 1)
        SET @Filtro_Tipos = @Filtro_Tipos + ', ''SQL_STORED_PROCEDURE'''


    IF (@Fl_Exporta_Functions = 1)
        SET @Filtro_Tipos = @Filtro_Tipos + ', ''SQL_INLINE_TABLE_VALUED_FUNCTION''' + ', ''SQL_SCALAR_FUNCTION''' + ', ''SQL_TABLE_VALUED_FUNCTION'''


    IF (@Fl_Exporta_Triggers = 1)
        SET @Filtro_Tipos = @Filtro_Tipos + ', ''SQL_TRIGGER'''


    IF (@Fl_Exporta_Views = 1)
        SET @Filtro_Tipos = @Filtro_Tipos + ', ''VIEW'''

        
    SET @Query = '
    IF (OBJECT_ID(''tempdb..##Exportacao_Objetos_Banco'') IS NOT NULL) DROP TABLE ##Exportacao_Objetos_Banco
    SELECT 
        IDENTITY(INT, 1, 1) AS Ordem,
        B.name AS Ds_Objeto,
        B.[type_desc] AS Ds_Tipo,
        (CASE B.[type_desc]
            WHEN ''SQL_INLINE_TABLE_VALUED_FUNCTION'' THEN ''TableFunction''
            WHEN ''SQL_SCALAR_FUNCTION'' THEN ''ScalarFunction''
            WHEN ''SQL_TABLE_VALUED_FUNCTION'' THEN ''TableFunction''
            
            WHEN ''SQL_STORED_PROCEDURE'' THEN ''StoredProcedure''
            
            WHEN ''SQL_TRIGGER'' THEN ''Trigger''

            WHEN ''VIEW'' THEN ''View''
        END) AS Nm_Tipo,
        A.[definition] AS Ds_Comando
    INTO
        ##Exportacao_Objetos_Banco
    FROM 
        [' + @Ds_Database + '].sys.sql_modules                 A   WITH(NOLOCK)
        JOIN [' + @Ds_Database + '].sys.objects                B   WITH(NOLOCK)    ON  A.[object_id] = B.[object_id]
    WHERE
        B.[type_desc] IN (' + @Filtro_Tipos + ')'


    EXEC(@Query)

    


    ------------------------------------------------------------------
    -- GERA OS ARQUIVOS .SQL
    ------------------------------------------------------------------

    DECLARE 
        @Contador INT = 1, 
        @Total INT = (SELECT COUNT(*) FROM ##Exportacao_Objetos_Banco),
        @Nm_Arquivo VARCHAR(MAX),
        @Comando VARCHAR(MAX),
        @Caminho VARCHAR(MAX),
        @Ds_Objeto VARCHAR(MAX),
        @CabecalhoArquivo VARCHAR(MAX)


    -- Apaga o arquivo destino, se já existir
    IF (@Fl_Arquivo_Unico = 1)
    BEGIN
        
        SET @Caminho = @Diretorio_Destino + '\Exportacao_Objetos.sql'

        IF (CLR.dbo.fncArquivo_Existe(@Caminho) = 1)
        BEGIN

            EXEC CLR.dbo.stpApaga_Arquivo
                @caminho = @Caminho -- nvarchar(max)

        END


        SET @CabecalhoArquivo = '
USE [' + @Ds_Database + ']
GO

'

        EXEC CLR.dbo.stpEscreve_Arquivo
            @Ds_Texto = @CabecalhoArquivo, -- nvarchar(max)
            @Ds_Caminho = @Caminho, -- nvarchar(max)
            @Ds_Codificacao = N'UTF-8', -- nvarchar(max)
            @Ds_Formato_Quebra_Linha = N'UNIX', -- nvarchar(max)
            @Fl_Append = @Fl_Arquivo_Unico -- bit
            

    END


    
    WHILE(@Contador <= @Total)
    BEGIN
    

        SELECT
            @Comando = Ds_Comando,
            @Nm_Arquivo = Nm_Tipo + '.' + Ds_Objeto + '.sql',
            @Ds_Objeto = Ds_Objeto
        FROM 
            ##Exportacao_Objetos_Banco
        WHERE
            Ordem = @Contador



        IF (@Fl_Gera_Com_Create = 0)
        BEGIN

            SET @Comando = REPLACE(@Comando, 'CREATE PROCEDURE ', 'ALTER PROCEDURE ')
            SET @Comando = REPLACE(@Comando, 'CREATE VIEW ', 'ALTER VIEW ')
            SET @Comando = REPLACE(@Comando, 'CREATE TRIGGER ', 'ALTER TRIGGER ')
            SET @Comando = REPLACE(@Comando, 'CREATE FUNCTION ', 'ALTER FUNCTION ')
            
        END



        IF (@Fl_Arquivo_Unico = 1)
        BEGIN
            
            SET @Nm_Arquivo = 'Exportacao_Objetos.sql'
            SET @CabecalhoArquivo = '
GO

--------------------------------------------------------------------
-- ' + @Ds_Objeto + '
--------------------------------------------------------------------
'

            EXEC CLR.dbo.stpEscreve_Arquivo
                @Ds_Texto = @CabecalhoArquivo, -- nvarchar(max)
                @Ds_Caminho = @Caminho, -- nvarchar(max)
                @Ds_Codificacao = N'UTF-8', -- nvarchar(max)
                @Ds_Formato_Quebra_Linha = N'UNIX', -- nvarchar(max)
                @Fl_Append = @Fl_Arquivo_Unico -- bit

        END
        ELSE BEGIN

            SET @Caminho = @Diretorio_Destino + '\' + @Nm_Arquivo
            SET @CabecalhoArquivo = 'USE [' + @Ds_Database + ']
GO

'

            EXEC CLR.dbo.stpEscreve_Arquivo
                @Ds_Texto = @CabecalhoArquivo, -- nvarchar(max)
                @Ds_Caminho = @Caminho, -- nvarchar(max)
                @Ds_Codificacao = N'UTF-8', -- nvarchar(max)
                @Ds_Formato_Quebra_Linha = N'UNIX', -- nvarchar(max)
                @Fl_Append = 0 -- bit

        END



        EXEC CLR.dbo.stpEscreve_Arquivo
            @Ds_Texto = @Comando, -- nvarchar(max)
            @Ds_Caminho = @Caminho, -- nvarchar(max)
            @Ds_Codificacao = N'UTF-8', -- nvarchar(max)
            @Ds_Formato_Quebra_Linha = N'UNIX', -- nvarchar(max)
            @Fl_Append = 1 -- bit
    

        SET @Contador = @Contador + 1


    END


END

Exemplos de utilização:

Neste primeiro exemplo, vou demonstrar como exportar os scripts em um único arquivo .SQL, que irá colocar um cabeçalho para diferenciar cada script e no começo do arquivo irá utilizar um USE [DATABASE] para alterar o banco atual.

EXEC dbo.stpExporta_Objetos_SQL
    @Ds_Database = 'msdb', -- sysname
    @Diretorio_Destino = 'C:\Users\dirceu.resende\Desktop\Exp\', -- varchar(600)
    @Fl_Arquivo_Unico = 1, -- bit
    @Fl_Gera_Com_Create = 1, -- bit
    @Fl_Exporta_Procedures = 1, -- bit
    @Fl_Exporta_Functions = 1, -- bit
    @Fl_Exporta_Triggers = 1, -- bit
    @Fl_Exporta_Views = 1 -- bit

SQL Server - Export programmability objects to sql script one file-1
SQL Server - Export programmability objects to sql script one file-1

SQL Server - Export programmability objects to sql script one file-2
SQL Server - Export programmability objects to sql script one file-2

Neste segundo exemplo, vou demonstrar como exportar os scripts em vários arquivos .SQL, sendo 1 arquivo por objeto, e no começo de cada arquivo terá o comando USE [DATABASE] para alterar o banco atual. Além disso, ao setar o parâmetro @Fl_Gera_Com_Create = 0, o CREATE PROCEDURE, por exemplo, será substituido por um ALTER PROCEDURE, caso os objetos já existam no database de destino e você queira apenas atualizar os objetos.

EXEC dbo.stpExporta_Objetos_SQL
    @Ds_Database = 'msdb', -- sysname
    @Diretorio_Destino = 'C:\Users\dirceu.resende\Desktop\Exp\', -- varchar(600)
    @Fl_Arquivo_Unico = 0, -- bit
    @Fl_Gera_Com_Create = 0, -- bit
    @Fl_Exporta_Procedures = 1, -- bit
    @Fl_Exporta_Functions = 1, -- bit
    @Fl_Exporta_Triggers = 1, -- bit
    @Fl_Exporta_Views = 1 -- bit

SQL Server - Export programmability objects to sql script multiple files each object in a file
SQL Server - Export programmability objects to sql script multiple files each object in a file

SQL Server - Export programmability objects to sql script multiple files each object in a file-2
SQL Server - Export programmability objects to sql script multiple files each object in a file-2

Uma vez que você gerou os seus vários scripts, pode agora executá-los em batch no servidor e database de destino utilizando a dica que disponibilizei no post SQL Server – Como executar em batch todos os scripts .sql de uma pasta ou diretório pelo SQLCMD

Obrigado pela visita e até o próximo post.