3

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?

Paul White
  • 83,961
  • 28
  • 402
  • 634
Marcello Miorelli
  • 16,170
  • 52
  • 163
  • 300
  • 1
    See my answer here for a way to automatically capture all changes my to the structure of your database http://dba.stackexchange.com/a/25052/10832 – Hannah Vernon Aug 27 '15 at 22:30

2 Answers2

7

Rather than saving your procedures in user tables, why don't you just set up a SSDT database project and use version control (TFS, Git, Mercurial, SVN or whatever you find appropriate) to take care of versioning?

SSDT or other tools such as Red Gate's SQL Source Control will help you in keeping track of versions and deploying your changes to your envirnoments.

spaghettidba
  • 11,266
  • 30
  • 42
  • +1 for source control. This is a much better answer, since saving the code in tablebackups will make your like more difficult. Version/Source control is the way to go - either Opensource or 3rd party tools. – Kin Shah Aug 27 '15 at 13:42
  • +1 for source control. I sometimes work in places where they don't use/ or servers can't connect to any source control. – Marcello Miorelli Sep 07 '15 at 15:56
  • 1
    Be the one to introduce it then! Extra smart points to your pocket :) – spaghettidba Sep 07 '15 at 15:58
3

You can use sys.sql_modules (MSDN):

select top 10 * from sys.sql_modules as m
inner join sys.procedures as p on m.object_id = p.object_id
where p.name ...

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.

  • DO: if not exists, create empty dummy SP and then alter SP
  • Don't do: if exists, drop SP and then create new SP

This query should put you in the right direction with the permissions:

select 'Proc' = SCHEMA_NAME(p.schema_id)+'.'+p.name
    , 'Type' = per.state_desc, 'Permission' = per.permission_name
    , 'Login' = pri.name, 'Type' = pri.type_desc 
    , *
From sys.procedures as p
left join sys.database_permissions as per on p.object_id = per.major_id
left join sys.database_principals as pri on per.grantee_principal_id = pri.principal_id
where ...
Julien Vavasseur
  • 10,109
  • 2
  • 27
  • 46