I often get requests to alter stored procedures, in both test, development and production environments.
I would like a simple way to save the current code of the stored procedure, before the changes, into a table in my "tablebackups" database – which is a database where I save records prior to update or delete them.
I am thinking about something like this:
sp_helptext 'sp_myprocedure'
the idea of usage is something like this:
select * into tablebackups.dbo.my_procedure_20150827_1220
from ss_save_my_procedure 'my_procedure'
has someone got a solution for this already developed?
Best Answer
You can use sys.sql_modules (MSDN):
It is better to alter them because you won't have to worry about existing permissions. If you drop and create the procedure, you have to set them back.
This query should put you in the right direction with the permissions: