T-SQL: (Migration) Export the definition of Stored Procedures


Very often we need to bulk export database objects and move them to another server. Manually we do this by right click on the database –> Tasks –> Generate Scripts…

In this example I show how we can export Stored Procedures with T-SQL and SQLCMD. This model gives us the flexibility to automate the process:

  1. SQL Agent Job
  2. SQLCMD
  3. T-SQL (Input) – Script Stored Procedures.sql
  4. T-SQL (Output) – Exported Stored Procedures.sql

In this example we’ll export the definitions of all the Stored Procedures (SP) in WideWorldImporters database.

First create a database, where store the SP that prints T-SQL code with more than 8000 characters. I found this SP long time ago in Internet and I use it successfully in my daily programming practice.

Next we need to runĀ in SSMS the SP that actually prints the SP definitions. At this point we define which SP definitions to export.

The marked lined 6 to 9 accept the parameters from SQLCMD. Now we need to comment the operators after the equal signs and type a standard T-SQL operators.

Lines 24 to 27 are the ones that control which SP definition to export.

After we make sure that the T-SQL is exporting everything properly, we need to fix lines 6 to 9 and save the code in file Script Stored Procedures.sql. In this example I save my files in C:\Temp.

Next we need to call the T-SQL with SQLCMD. We create an empty file in C:\Temp and rename it to Script Stored Procedures.bat. Open it in Notepad++ and add the code.

The options that we use are:

  • -U – Username
  • -P – Password
  • -S – “ServerName\InstanceName”
  • -d – “DatabaseName”
  • -i – Input file (“C:\Temp\Script Stored Procedures.sql”)
  • -o – Output file (“C:\Temp\Exported Stored Procedures.sql”)
  • -v – Boolean variable to list the exported SPs in the beginning of the result (ListStoredProcedures=1)
  • -v – Boolean variable to add If SP exists, delete it statement before the Create SP statement (AddCleanUp=1)
  • -v – Boolean variable to add GRANT EXECUTE ON statement (AddGrantPermission=1)
  • -v – String variable. The role that the permission is granted to (PermissionName=”My Role”)

To test the package, we browse C:\Temp in Command Prompt (cmd.exe) and execute

As expected, a new file Exported Stored Procedures.sql is created in C:\Temp.

Keep it simple :-)

Share

Leave a comment

Your email address will not be published. Required fields are marked *