¡Hola, chicos!
Buen día.

Introducción

En esta publicación, le mostraré cómo generar scripts de creación de objetos de programación de SQL Server (Funciones, Activadores, Procedimientos almacenados y Vistas) y exportarlos a archivos .SQL usando consultas de vista de catálogo. De esta forma podemos automatizar la generación de estos scripts.

Otra necesidad que me llevó a crear este script fue que en mi base de datos de funciones útiles tengo cientos de funciones SP's y CLR y T-SQL juntas. A veces cuando quiero copiar funciones y Procedimientos Almacenados escritos en T-SQL, ignorando el CLR, no tengo una forma sencilla de hacerlo, porque la interfaz de SQL Server Management Studio no permite filtrar funciones y procedimientos almacenados T-SQL y CLR, generando el script para todos ellos y luego necesito excluir los scripts que son CLR.

SQL Server - Tasks - Generate and Publish Scripts
SQL Server - Tareas - Generar y publicar scripts

Consultar el código fuente de los objetos.

Realizar una consulta simple en vistas de catálogo sys.sql_modules y sys.objetos, podemos obtener el código fuente de los objetos de programación de SQL Server en la base de datos actual:

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: consulta para generar y publicar scripts

Cómo exportar objetos de programación a archivos .SQL

Como ya tenemos el código fuente de cada objeto, sólo necesitamos exportarlo a archivos de texto y nuestra tarea estará completa. Para facilitar este proceso, pondré a su disposición un Procedimiento Almacenado que ya realiza esta tarea, iterando entre cada objeto y generando el script de los objetos.

Como requisito previo para que funcione, necesitará los siguientes objetos, cuyo código fuente está disponible en mi publicación. SQL Server: cómo enumerar, leer, escribir, copiar, eliminar y mover archivos con CLR (C#):
– fncFile_Exists
– stpDelete_File
– stpWrite_File

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

Ejemplos de uso:

En este primer ejemplo, demostraré cómo exportar los scripts a un solo archivo .SQL, el cual colocará un encabezado para diferenciar cada script y al comienzo del archivo usará un USE [DATABASE] para cambiar la base de datos actual.

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: exporte objetos de programabilidad a un script SQL en un archivo-1

SQL Server - Export programmability objects to sql script one file-2
SQL Server: exporte objetos de programabilidad a un script SQL en un archivo-2

En este segundo ejemplo, demostraré cómo exportar los scripts a varios archivos .SQL, 1 archivo por objeto, y al principio de cada archivo estará el comando USE [DATABASE] para cambiar la base de datos actual. Además, al configurar el parámetro @Fl_Gera_Com_Create = 0, CREATE PROCEDURE, por ejemplo, será reemplazado por ALTER PROCEDURE, si los objetos ya existen en la base de datos de destino y solo desea actualizarlos.

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: exporte objetos de programabilidad a scripts SQL en varios archivos, cada objeto en un archivo

SQL Server - Export programmability objects to sql script multiple files each object in a file-2
SQL Server: exporte objetos de programabilidad a scripts SQL en varios archivos, cada objeto en un archivo-2

Una vez que haya generado los distintos scripts, ahora puede ejecutarlos por lotes en el servidor y la base de datos de destino siguiendo el consejo que le proporcioné en la publicación. SQL Server: cómo ejecutar por lotes todos los scripts .sql en una carpeta o directorio usando SQLCMD

Gracias por visitarnos y nos vemos en el próximo post.