"Include IF NOT EXISTS clause" impacts DROP scripting

"Include IF NOT EXISTS clause" impacts DROP scripting by Jean-Nicolas BERGER

Closed

as Fixed

Type:	Bug
ID:	624075
Opened:	11/23/2010 12:42:21 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

The "Include IF NOT EXISTS clause" in the Scripting options impacts the DROP scripts generated by the Wizard (Script Table as / DROP To / ...) in SSMS in the Denali version while it didn't impacted it in the SQL2008R2 version.

Details (expand)

English

SQL Server Denali CTP 1

Tools (SSMS, Agent, Profiler, Migration, etc.)

Windows Vista 64-bit SP1

French

In the Tools menu, choose Options / SQL Server Object Explorer / Scripting / Object scripting options, and set the "Include IF NOT EXISTS clause" to False.
Choose a user database in the Object Explorer window. Right-click on a user table, and choose Script Table as / DROP To / New Window.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xxxx]') AND type in (N'U'))
is not present in the script, while it is if the option "Include IF NOT EXISTS clause" is valued to True.

An option 'Incluse IF NOT EXISTS clause" should not impact a "IF EXISTS" part of a statement.

X64

0 attachments

Posted by Damon Sutherland on 5/3/2011 at 10:34 AM

Is there a chance we can separate the options to apply IF EXISTS ... and IF NOT EXISTS ... ? In our environment, at least for stored procs, we prefer to drop then create (without the sp_exectue_sql). It saves a bunch of effort when retrieving scripts from svn.

Posted by Microsoft on 1/18/2011 at 3:33 AM

Hi

We were able to reproduce this issue, and we have fixed this issue. "Include IF NOT EXISTS clause" has been re-worded to "Check for object existence". This will reflect in an upcoming release of SQL Server.

Regards

Bishal Prasad

Posted by Microsoft on 11/29/2010 at 4:58 AM

Hi:

Greetings from the SQL Server Manageability Team.

Thanks for writing in and giving valuable feedback. We observe what you point out, and agree that the wording is a bit misleading. We are triaging this bug against our current set of committed deliverables, and try to reword/improve this option, in a SQL Server Release in the near future.

Thanks Aaron for chiming in as well.

Cheers,

Chandramouli

Posted by aaronbertrand on 11/23/2010 at 3:22 PM

Some people want an error returned if they try to drop a table and it doesn't exist. In 2008 R2 I think the "IF EXISTS" is always there regardless of this option; I think the true intention is to make it both ways - check if it exists on drop, check if it doesn't exist on create. The option is just worded poorly to reflect this.