Share via

Delete Constraint Without Knowing Its Name ??

  • Article

Question

Thursday, October 4, 2007 1:42 PM

In SQL Server 2005, I hava a client where I do not have access to their SQL Server.  I update the database structure by giving them scripts which they run.  As I update the structures I occasionally need to delete a constraint, then typically recreate it later.  Usually I use this type of snippet:

IF  EXISTS (SELECT * FROM sys.default_constraints
    WHERE object_id = OBJECT_ID(N'[dbo].[ConstraintName]')
    AND parent_object_id = OBJECT_ID(N'[dbo].[tablename]'))
 ALTER TABLE [dbo].[tablename] DROP CONSTRAINT [ConstraintName]

This assumes I know the constraint name.  A lot of the older constraints have random type names which I assume are different on their server than mine; they were not explicitly given names when created, so they would heve been given names by SQL Server.

Is there a way to delete a constraint without knowing its name? For example, delete all constraints associated with a field, or all constraints ssociated with a table?

Management Studio is an option, but for my client that involves a higher degree of paperwork and permission seeking than just running a script.

Many thanks,
Mike Thomas

All replies (2)

Thursday, October 4, 2007 2:19 PM âś…Answered

One way would be to write a loop/cursor on the table in question and use dynamic SQL:

 

Code Block

declare @name VARCHAR(100)
while (select count(*) FROM sys.default_constraints WHERE object_name(parent_object_Id) = 'Employee') > 0
begin
 set @name = (SELECT TOP 1 name FROM sys.default_constraints WHERE object_name(parent_object_Id) = 'Employee')
 exec('alter table HumanResources.Employee drop constraint ' + @name)

end

 

 

Not sure if this suits your needs or not?

 

Would they be able to provide you with a dump of the sys.default_constraints catalog? Would help you out in the future i guess.

 

HTH!

Thursday, October 4, 2007 6:39 PM

Many thanks - ust what I needed.
Mike Thomas