Hey Guys!
Bom dia.
Introduction
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.
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:
|
1 2 3 4 5 6 7 |
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] |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 |
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.
|
1 2 3 4 5 6 7 8 9 |
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 |
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.
|
1 2 3 4 5 6 7 8 9 |
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 |
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.
Portuguese 
English
Tem como fazer essa exportação sem o uso do codigo em c#? Fazendo só com o sql?