4


I'm using sql server management studio 2008 to try and generate an alter script for each of my stored procedures in order to save the scripts for each revision. I can easily generate an alter script for each individual procedure, but I'm not trying to go through a hundred stored procedures manually.

I know that SSMS has an automated generate scripts function under task, but the only options are create, drop and create, and drop. I cant seem to figure out how to enable alter. I've already searched through many SO articles, as well as a little digging in msdn, and I've come up with nothing.

I'm hoping that the fine people of stackoverflow will be up to the challenge. Thanks in advance

Improve this question
1
  • i had this request for long, but i never asked until now, good you asked it before :) Commented Jun 23, 2024 at 12:00

4 Answers 4

Reset to default
10

Use CHECK FOR OBJECT EXISTENCE option in Advanced Scripting Options. Script will contain set of IF NOT EXISTS... CREATE commands and below ALTER for each stored procedure you wanted to script.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Confirmed: works in SSMS 2014. The only "simply correct" answer here. Will produce script like: IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MySP]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[MySP] AS' END GO ALTER ....
seems this works for SPs but not for UDFs
Can't believe I haven't discovered this option until now. Thanks. Reminds me of the good old SQL Server 2000 days.
4

It's not a very elegant solution but it would definitely work. Why not generate create script and then just replace all occurrences of CREATE PROCEDURE with ALTER PROCEDURE.

Improve this answer

1 Comment

Also a good Idea, and that's what I'm trying to do now, but there's 395 stored procedures so if it could be automated, yeahh that'd be greaaat.
2

You can generate stored procedures automatically from SQL SERVER Management Studio as following: 1) Right click on your database -> Tasks -> Generate Scripts 2) Select "specific database objects" then choose tables / stored procedures you want to generate script for them then press "Next" 3) In this window you can choose where you want to save your script, then you will find an option "Advanced", click it. Then you will find an option "Script DROP and Create",there are three options: Create, Drop, Drop and Create. Choose one as you want. 4) Then press ok, then "Next" and the script will be generated automatically.

If you want to change it from Create to Alter, just do "replace all" operation using any text editor. Hope this answer helps others.

Improve this answer

Comments

-2

Well, Drop/Create is the same as alter. By stating that you would like to use alter then you are certain that the target objects exists. Why not just select the group from the object explorer and right click select DROP/Create. This should do the same.

Improve this answer

6 Comments

Mainly for security concerns. If something goes wrong mid drop,it might not be a good thing. I appreciate the help but I was hoping it wouldn't have to come to that.
Have you used the RedGate tool belt? Red gate has a tool that will create packages that will try an all or nothing commit and rollback previous commands on failure.
I haven't actually, sounds interesting though
CAUTION Chaining a DROP and CREATE call for a procedure is CERTAINLY NOT the same as an ALTER statement, as any permissions you have specified for the procedure will be lost if you drop the procedure and then recreate it. For this reason, -1.
If You look into "red-gate" scripting which I recommend you would see that this is not an issue. And it scales well with multiple endpoints
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.