How to drop a constraint where the constraint name is variable?

• Unnikrishnan.C

  Old Hand

  Points: 320

  More actions

  April 21, 2006 at 1:45 am

  #112587

  Hi,

  In our product database we created a Primary Key Constraint without a specific name. The PK Constraint name may be different in different database servers. For e.g, PK__TestTable__753864A1 .

  Now we want to drop this table's constraint using a runtime SQL script during the product install. Something like as follows:

  DECLARE @val VARCHAR(50)

  select @val = name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsConstraint') = 1 AND name like 'PK__TestTable%'

  ALTER TABLE [dbo].[VOIP_CUEUsers] DROP CONSTRAINT @val

  The constraint's name will be a variable and how can we drop it? The above query throws an error as you know.

  Thanks in advance.

  Regards

  Unnic

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 21, 2006 at 1:51 am

  #633422

  You'll need to use dynamic SQL in this case, as an alter table can't take a variable.

  DECLARE @val VARCHAR(50)

  select @val = name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsConstraint') = 1 AND name like 'PK__TestTable%'

  EXECUTE ('ALTER TABLE [dbo].[VOIP_CUEUsers] DROP CONSTRAINT ' + @val)

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Unnikrishnan.C

  Old Hand

  Points: 320

  More actions

  April 21, 2006 at 2:42 am

  #633428

  Thanks Gila..

  But the above query throws an error:

  Server: Msg 170, Level 15, State 1, Line 1

  Line 1: Incorrect syntax near 'CONSTRAINT'.

  Any idea how can I solve it?

  Regards

  Unnic

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 21, 2006 at 3:28 am

  #633443

  If you print the string instead of executing it, what does it contain?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Unnikrishnan.C

  Old Hand

  Points: 320

  More actions

  April 21, 2006 at 3:43 am

  #633444

  Nothing is getting printed.

  For e.g:

  DECLARE @val VARCHAR(50)

  select @val = name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsConstraint') = 1 AND name like 'PK__TestTable%'

  PRINT ('ALTER TABLE [dbo].[VOIP_CUEUsers] DROP CONSTRAINT ' + @val)

  This yield nothing. But there is no error!

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 21, 2006 at 3:56 am

  #633445

  What does this return?

  DECLARE @val VARCHAR(50)

  SELECT name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsConstraint') = 1 AND name like 'PK__TestTable%'

  select @val = name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsConstraint') = 1 AND name like 'PK__TestTable%'

  PRINT ' @val has the value ' + ISNULL(@Val,'')

  PRINT 'ALTER TABLE [dbo].[VOIP_CUEUsers] DROP CONSTRAINT [' + ISNULL(@Val,'') + ']'

  Is there a pk with the name starting PK__TestTable? A better way to get the pk name of the table 'VOIP_CUEUsers' something like this

  SELECT name FROM sysobjects WHERE OBJECTPROPERTY(id, N'IsPrimaryKey') = 1 AND OBJECT_NAME(parent_obj) = 'VOIP_CUEUsers'

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Unnikrishnan.C

  Old Hand

  Points: 320

  More actions

  April 21, 2006 at 4:03 am

  #633447

  Thank you very much Gila..

  It was my mistake that I used PK_TestTable, but the table, TestTable and the constraint was not existing. When I used an existing table it worked !

  Thanks

  Regards

  Unnic