Hello everybody!
Good day.
Introduction
In this post, I will show you how to generate SQL Server programming object creation scripts (Functions, Trigger, Stored Procedures, and Views) and export them to .SQL files using catalog view queries. This way we can automate the generation of these scripts.
Another need that led me to create this script was that in my database of useful functions, I have hundreds of SP's and CLR and T-SQL functions together. Sometimes when I want to copy the functions and Stored Procedures written in T-SQL, ignoring the CLRs, I don't have a simple way to do this, because the SQL Server Management Studio interface does not allow filtering T-SQL and CLR functions and stored procedures, generating the script for all of them and then I need to delete the scripts that are CLR.
Querying the source code of objects
Performing a simple query in catalog views sys.sql_modules e sys.objects, we can get the source code for SQL Server programming objects in the current database:
| 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] |
How to export programming objects to .SQL files
Since we already have the source code for each object, we now just export it to text files and our task is done. In order to facilitate this process, I will make available to you a Stored Procedure that already does this task, iterating between each object and generating the script of the objects.
As a prerequisite for it to work, you will need the objects below, whose source code is available in my post. SQL Server - How to list, read, write, copy, delete and move files with 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 |
Usage examples:
In this first example, I will demonstrate how to export scripts in a single .SQL file, which will put a header to differentiate each script and at the beginning of the file will use a USE [DATABASE] to change the current database.
| 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 |
In this second example, I will demonstrate how to export scripts to multiple .SQL files, where 1 is file by object, and at the beginning of each file will have the USE [DATABASE] command to change the current database. Also, by setting the @Fl_Gera_Com_Create = 0 parameter, CREATE PROCEDURE, for example, will be replaced with an ALTER PROCEDURE if objects already exist in the target database and you just want to update the objects.
| 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 |
Once you have generated your various scripts, you can now batch run them on the target server and database using the tip I provided in the post. SQL Server - How to Batch All .sql Scripts in a Folder or Directory by SQLCMD
Thanks for stopping by and see you in the next post.
Is there a way to do this export without using the code in C#? Doing only with sql?