Scripting a Drop on a Constraint

Question

Scripting a Drop on a Constraint

JKSQL

SSCrazy Eights

Points: 8481
More actions
January 25, 2011 at 7:38 am

#232634

If I right click on a specific constraint and script it as a drop I get this:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[def_Employees_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [BusinessMgmt].[Employees] DROP CONSTRAINT [def_Employees_CreatedDate]
END
GO
That does not work.It will not find the object ID. I then have to alter to this:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE Name= 'def_Employees_CreatedDate' AND type = 'D')
BEGIN
ALTER TABLE [BusinessMgmt].[Employees] DROP CONSTRAINT [def_Employees_CreatedDate]
END
GO
It will then find it. Am I doing something wrong or is this in fact a bug. If I try and look up object id for constraints it always comes up null. Just want to make sure I am doing it by best practice

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I'm going to go out on a limb here and say it may be a bug. I tried it on a check constraint on a table in AdventureWorks, and the code produced is correct. Tried it on a default constraint on the same table and got what you did.

What probably should be generated should look like this:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[BusinessMgmt].[def_Employees_CreatedDate]') AND type = 'D')

BEGIN

ALTER TABLE [BusinessMgmt].[Employees] DROP CONSTRAINT [def_Employees_CreatedDate]

END

GO

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 2

Agreed Check constraints seem to work for me also. So I will rack it up as a bug

WayneS SSC Guru Points: 95461 More actions · Answer 3

Well, I don't see a problem with this:

1. constraints are a separate object, and are stored in the sys.objects (or sysobjects) views.

2. the scripted commands for a drop/create work for me w/ SSMS 2008.

3. it would be better to do a "if exists (select 1" instead of select *.

What I have a problem with, in SQL 2008, is that the generated script is using sysobjects instead of sys.objects.

You'll do know that the object_id allows for a second parameter... the type? You could use:

if object_id(N'[def_Employees_CreatedDate]','D') IS NOT NULL

ALTER TABLE xxx DROP CONSTRAINT [def_Employees_CreatedDate]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

WayneS (1/26/2011)
Well, I don't see a problem with this:
1. constraints are a separate object, and are stored in the sys.objects (or sysobjects) views.
2. the scripted commands for a drop/create work for me w/ SSMS 2008.
3. it would be better to do a "if exists (select 1" instead of select *.
What I have a problem with, in SQL 2008, is that the generated script is using sysobjects instead of sys.objects.
You'll do know that the object_id allows for a second parameter... the type? You could use:
if object_id(N'[def_Employees_CreatedDate]','D') IS NOT NULL
ALTER TABLE xxx DROP CONSTRAINT [def_Employees_CreatedDate]

Here is the code generated by SSMS to drop a default constraint:

USE [AdventureWorks]

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Employee_CurrentFlag]') AND type = 'D')

BEGIN

ALTER TABLE [HumanResources].[Employee] DROP CONSTRAINT [DF_Employee_CurrentFlag]

END

GO

Taking the SQL from inside the IF EXISTS as is and running it returns nothing. Adding the [HumanResources]. to the code, it then returns a 1 row result set.

SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Employee_CurrentFlag]') AND type = 'D';

SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[HumanResources].[DF_Employee_CurrentFlag]') AND type = 'D';

The difference between the two pieces of code, the first is assuming the schema for DF_Employee_CurrentFlag is dbo, when it is actually HumanResorces.

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 5

Thanks for that help. It is the Schema that is not scripting out which is a bug but I can work around. When looking for the constraint I need to add the schema and it then shows up using object ID.

[HumanResources].[DF_Employee_CurrentFlag]

vs

[DF_Employee_CurrentFlag]