SSMS: Generate Scripts for Stored Procedures Without Dynamic SQL
Problem Definition
Recently, there was a thread in the forums asking whether there is any way to avoid the dynamic SQL in the generated code while scripting out the stored procedures from SQL Server Management Studio (SSMS). This wiki article discusses a possible solution for the issue.
Steps To Reproduce the Problem
- Open SQL Server Management Studio (SSMS)
- Connect to your database
- Choose Tools -> Options from top menu and make sure Include IF NOT EXISTS clause option is set to True in scripting properties
- Open Object Explorer
- Expand ServerName -> DatabaseName -> Programmability -> StoredProcedures
- Choose your stored procedure to script out as below
-
- You will be provided with script as below
- As you see the generated script would involve a dynamic SQL part as a call to the system stored procedure sp_executesql
Reason
The main reason for the above issue is because of the setting we did in the scripting properties to include IF NOT EXISTS clause. But this is required in the script to ensure a smooth migration across environments i.e from development to QA to production while doing incremental changes.
Resolution
This issue can be resolved by using the below steps instead for scripting out the objects
Choose Tools -> Options from top menu and make sure Include IF NOT EXISTS clause option is set to False in scripting properties
Expand ServerName -> DatabaseName -> Programmability -> StoredProcedures
Choose script out option as below
You will now see that the generate script does not have dynamic SQL part as per below
If you need to do scripting for multiple objects simultaneously you can use object explorer detail window as below
You will get script for all objects included in a single file as below
Summary
As shown by the above steps, we can make use of Script As -> DROP And CREATE option to avoid generating scripts with dynamic SQL for stored procedures.