Drop constraint if exists

• smknox

  Hall of Fame

  Points: 3275

  More actions

  January 18, 2008 at 11:54 am

  #178373

  I get a syntax error (near "constraint") with this:

  if exists (select * from dbo.sysobjects where id =

  object_id(N'[dbo].[DF_AppointmentType_Description1]')) and OBJECTPROPERTY(id, N'IsConstraint') = 1)

  DROP CONSTRAINT [dbo].[DF_AppointmentType_Description1]

  What am I missing?

  Thanks so much

  Sally

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  January 18, 2008 at 1:50 pm

  #769880

  Constraints, like indexes, exists within a table, so any DDL must reference the table. I also avoid using builtin metadata funtions like object_id and OBJECTPROPERTY, which do not support the four part naming convention, so cannot be used with linked servers or with databases other than the current.

  IF EXISTS (

  select 1

  from dbo.sysusersas TableOwners

  joindbo.sysobjectsas Tables

  on Tables.uid= TableOwners.uid

  joindbo.sysobjectsas Constraints

  on Constraints.parent_obj = Tables.id

  whereConstraints.type = 'D'

  whereTables.type = 'U'

  andTableOwners.name = 'dbo'

  andTables.name= 'AppointmentType'

  andConstraints.name = 'DF_AppointmentType_Description1'

  )

  begin

  alter table dbo.AppointmentType drop constraint DF_AppointmentType_Description1

  end

  SQL = Scarcely Qualifies as a Language

• Jeff Moden

  SSC Guru

  Points: 1004312

  More actions

  January 18, 2008 at 5:19 pm

  #769910

  Alter table won't support a 4 part naming convention, anyway... object functions and Alter table all support 3 part naming conventions... why not use them?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)