Home Dashboard Directory Help
Search

Drop Create Script missing (IF EXISTS...) by Π-3.14 - John Piraino


Status: 

Closed
 as By Design Help for as By Design


4
0
Sign in
to vote
Type: Bug
ID: 761559
Opened: 9/6/2012 6:14:11 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

For as long as I could remember using the scripting tools within SQL Server Management Studio, or Enterprise Manager, the Create / Drop script tool would always create the IF EXISTS ... portion of the script. This is no longer in the script. Sure, the sys.sp_dropextendedproperty script is there, this does not work on older versions of SQL server. What gives?
Details
Sign in to post a comment.
Posted by ghendric on 11/15/2013 at 9:11 AM
Setting the "Check for object existence" to True in SQL 2012 causes an "IF NOT EXISTS " call for creating the proc too.. How do you turn that off for creating the proc but leave it on for dropping the proc? Here's an example of what i get when I turn that feature on. I

USE MyDataBase
GO

/****** Object: StoredProcedure [dbo].[RemoveIcabAssemblies]    Script Date: 11/15/2013 12:07:46 PM ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MyProc]
GO

/****** Object: StoredProcedure [dbo].[RemoveIcabAssemblies]    Script Date: 11/15/2013 12:07:46 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[MyProc]
AS
    BEGIN
        -- code goes here
    END

'
END
GO
Posted by SPFriend on 8/27/2013 at 10:26 AM
I agree, we do need the option- If Exists, then drop, in generating database script.
That is very useful
Posted by marsovo on 11/7/2012 at 7:43 AM
Why? This is now a colossal nuisance in creating upgrade/migration scripts. Before, you could generate a drop/create script, and it would fit any scenario: if the object exists, it gets recreated; if it doesn't exist, it gets created.

Now, with "check existence" turned off, the 'IF EXISTS' before the DROP isn't there, so the script will generate an error if the upgrade script is creating a new object. But with 'check existence' turned on, the create and alter scripts are a mess of dynamic SQL that is a real nuisance to work with.
Posted by Microsoft on 10/17/2012 at 11:05 AM
The issue will be closed as by design.

Thank you
Alex Grach [MSFT]
Posted by Microsoft on 10/16/2012 at 2:19 PM
Thank you for reporting this issue. The Management Studio is using a different scripting option to check for object existence in SQL Server 2012. This option is disabled by default. In order to get existence checks in generated scripts, please set the following option in SSMS:

Tools -> Options -> SQL Server Object Explorer -> Scripting -> Check for object existence -> True

Thanks,
Fatemeh Alavizadeh
Posted by Microsoft on 9/13/2012 at 5:38 PM
Thank you for reporting this issue - we are investigating and will update you when we have more information.

Thanks,

Alex Grach [MSFT SQL SERVER]
Sign in to post a workaround.