Scripting Objects in SSMS - sp_executesql

Question

I have been scripting objects (tables, views, sps, etc.) since SS2K.  When I do it, I get a script that what you would think, for example:

CREATE PROCEDURE [dbo].[pr_my_stored_proc] @parm1, @parm2, etc.

Now, for some reason, I get the following when I try to script my object:

EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE dbo].[pr_my_stored_proc] @parm1, @parm2, etc. ... a big long string.
   

This would be OK except that I want to modify the code. In its "text" form, I have to remove double "single quotes" everywhere and it is a royal pain.

Would anyone know if this is a recent change or is there a setting that could change it back to the original method?

Thanks,

Tom

Answer 1

What is your SQL Server version?

Go to Tools/Options/SQL Server Object Explorer/Scripting and examine the options there. The option that may cause the problem is 

Include if not exists clause

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

in SSMS (I am using SQL 2008)

Go to Tools-->Options-->under Object Explorer-->Scripting--> Make sure "Include IF NOT EXISTS clause" is set to false this way you will get rid of sp_executesql part. 

Answer 3

If you go to the Tools/Options menu then look for the scripting options

http://sqlblog.com/blogs/andy_leonard/archive/2010/07/19/why-does-ssms-generate-create-scripts-with-exec-dbo-sp-executesql.aspx

---

Chuck Pedretti | Magenic – North Region | magenic.com

Answer 4

Chiraq ... that is exactly what the problem was.  Apparently, I had been experimenting with different options and had set that feature to TRUE and left it without knowing what it did.

You are a lifesaver! 

Many Thanks!

Answer 5

In case there is someone using 2012, it's called "Check for object existence" in SSMS 2012.