correct existing stored procedures

steven Evans

I have an existing project which has large number of stored procedures. They were written a while age I would like to run them though Sql Prompt to have a standard format and Analyse them for improvements.

Is there a way to batch this?   

Jon_Kirkwood

Hi @steven Evans

Thank you for reaching out on the Redgate forums regarding your bulk update of stored procs.

 

It would certainly be possible to conduct some bulk operations of SQL Prompt through SSMS if you have an active SQL Toolbelt & SQL Toolbelt Essential bundles.

Looking at your account, I do believe you have a Toolbelt Essentials license and should be able to follow these guides.

 

There are processes for bulk operations in SSMS directly, details on those can be found here:

https://documentation.red-gate.com/sp/features-available-only-in-sql-toolbelt-essentials-and-sql-toolbelt/bulk-operations/bulk-operations-in-ssms/bulk-formatting

If active you will have a Bulk Actions menu item under SQL Prompt, you can format all open tabs or point it to a folder of SQL Files to format at once.

 

It is also possible to run bulk operations directly from the command line, details on those can be found here:

https://documentation.red-gate.com/sp/features-available-only-in-sql-toolbelt-essentials-and-sql-toolbelt/bulk-operations/bulk-operations-via-the-command-line

 

 

I tested a process to bulk update a Stored Procedures section of my database and used the following process:

 

1 - Using SQL Compare, compare the database to an empty scripts folder. I applied a filter so only Stored Procedure objects were deployed.

 

2 - Using Bulk Operations in SQL Prompt, I applied a formatting style to the folder of SQL code I just created.

 

3 - Spot checked the code to make sure it looked like I expected it to

 

4 - Using SQL Compare again, compare the scripts folder back to the database. Noting many Stored Proc's were 'in both, but different' as expected

 

5 - Deploy changes using SQL Compare and verify the data in my database.

 

 

I would certainly recommend taking a backup of your database and testing this on a small batch of procedures first to make sure it works as expected. 

However, this process should allow you to conduct a bulk format of SQL objects at once.