SQL Server - How to export the source code of all Stored Procedures, Functions, Views and Triggers from a database to .sql files

Views: 3.589
Reading Time: 5 minutes

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.

SQL Server - Tasks - Generate and Publish Scripts

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:

SQL Server - Query to Generate and Publish Scripts

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

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.

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

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

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.

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

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.