Problem w/scripting options in SQL Server 2005 1

[jacktek]

We need to be able to script objects (tables, user functions, stored procedures) with the below structure:
if exists(---) then drop procedure xxx
create procedure xxx

This is available in SQL Server 2000, but I have not been able to do it in SQL Server 2005.

It seems SQL Server 2005 has
alter procedure xxx,
or
create procedure xxx,
or
drop procedure xxx,

but no option to
if exists then drop procedure xxx
create procedure xxx

What's ironic is that the template for creating stored procedure in our SQL Server 2005 instance is set up with
if exists then drop xxx
create xxx
but will they script that way ?? Nope.

This puts a big crimp in our established procedures for generating new service packs for our customers. It seems a really stupid thing for MS to do. Or am I the dummy (LOL), am I just missing something ?

Thanks

 

[mrdenny]

If you use the scripting wizzard instead of right clicking on the object you can get this, but it will take a couple of passes through the wizzard. The first to generate the drop script and the second to generate the create script.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog