CODEWITHSUNDEEP
sqlsql-serverconstraints
Dave
Dave

Reputation: 1775

Dynamically delete multiple foreign key constraints

No Idea how this happened, but we have multiple of the same FK constraint on the same Column. I think they were named by some management studio type thing, meaning they aren't a default constraint, and they aren't named by a human.

I have this query to find the constraint names

SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_NAME = 'Device' 
AND CONSTRAINT_NAME like '%Address%';

How can I drop each constraint in the result set? I guess I'd have to iterate through and do a drop constraint?

Upvotes: 0

Views: 1627

Answers (3)

MelgoV
MelgoV

Reputation: 656

Just try this:

Declare @Query Nvarchar(Max)
Declare @name Nvarchar(150)

Declare Cur Cursor For

SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_NAME = 'Device' 
AND CONSTRAINT_NAME like '%Address%';

Open Cur

Fetch Next From Cur
Into @name

While @@fetch_status=0
Begin

    Set @Query='Alter table Device 
    DROP CONSTRAINT '+Ltrim(Rtrim(@name))

    Execute sp_executesql @Query

    Fetch Next From Cur
    Into @name
End
Close Cur
Deallocate Cur

Go

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

You certainly don't need a cursor or any kind of looping for this. Just use sql to build your sql statements. Run the below query to generate your sql. Then just copy and paste into a new tab. Voila! This approach even allows you skip dropping some if you want.

select 'ALTER TABLE ' + TABLE_NAME + ' DROP CONSTRAINT ' + CONSTRAINT_NAME + ';'
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_NAME = 'Device' 
AND CONSTRAINT_NAME like '%Address%';

If you totally want to automate it you could use a variable to hold the results and execute it.

Upvotes: 1

Juc
Juc

Reputation: 227

The only way I can imagine doing it is like you said, iterate through each time and drop it using a cursor (http://msdn.microsoft.com/en-us/library/ms180169.aspx) or writing dynamic SQL.

Upvotes: 1

Related Questions