>>>Hi,
>>>
>>>I have been looking on various sites for the code to drop a constraint if I know the name of the constraint. Each site has a different code. I am confused. I would like something that would work on SQL Server 2005 and SQL Server 2008. What would you recommend?
>>>
>>>TIA.
>>>
>>>UPDATE. This is the code that works on my SQL Server 2008. Does it look like the right one?:
>>>
>>>

>>>IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK_table_name_fk_field]') 
>>>     AND parent_object_id = OBJECT_ID(N'[dbo].[table_name]'))
>>>	BEGIN
>>>		ALTER TABLE [dbo].[table_name] 
>>>		DROP CONSTRAINT [FK_table_name_fk_field]
>>>	END
>>>

>>
>>I don't think you need an extra check for parent_object_id. I believe the constraint names should be unique across database, not table wise (I'm going to test this quickly).
>>
>>OK, I verified - the constraint name must be unique in the database.
>
>You are saying that if by accident I try to name constraints of two different tables with the same name I will get error?

Yes, exactly. It will not let you create another constraint with the same name. That's why Pinal Dave recommends to include table name in the constraint name.

This is the test I ran:

create table test
(id int identity, val decimal(10,2))

alter table test add constraint pk_ID primary key (id)

create table test2 (id int identity, val decimal(10,2))
alter table test2 add constraint pk_ID primary key (id)

and the error I got is:
Msg 2714, Level 16, State 5, Line 2
There is already an object named 'pk_ID' in the database.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.