Delete all the foreign key constraints that are refrenced to a specific table

 

       When I was working with one of my migration project I faced this problem, we have to drop one master table that associated with several child tables and we have to recreate this master table with new structure.  The script is already Witten by some developer, the process they are following is: Creating one temp table same as master table putting all the data into temp table from master table, dropping all constraints using hard code (directly mentioning the name of constraints) , then dropping the master table and recreating the master table, also recreating the all constraints and taking back the data from temp table to master table using some conversion process.

     Problem: Developer are created some new child tables and relations, so while dropping the table I faced problem, its not allowing me to drop the table due to new relations .

Solution: Now I am using following scripts to drop all the constraints dynamically. So even developer add some new constraints it will handle.

We can also create all dropped constraints dynamically.

  

Note : Please don't use this scripts on production database.

 

 

 

 

 

--Query to create drop constraints
select 'ALTER TABLE '+ OBJECT_NAME(fkc.parent_object_id)
 +'  DROP CONSTRAINT '+OBJECT_NAME(fkc.constraint_object_id)
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkc on fk.parent_object_id = fkc.parent_object_id and fk.referenced_object_id = fkc.referenced_object_id
inner join sys.tables tb on fk.parent_object_id = tb.object_id
inner join sys.columns col on fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
inner join sys.columns col1 on fkc.referenced_object_id = col1.object_id and fkc.referenced_column_id = col1.column_id
where  fk.referenced_object_id = object_id('mastertable_name')


--Query to Add constraints
select 'ALTER TABLE '+ OBJECT_NAME(fkc.parent_object_id)
 +' ADD CONSTRAINT '+OBJECT_NAME(fkc.constraint_object_id)
 +' FOREIGN KEY ('
 + col.name 
 + ') REFERENCES ' + OBJECT_NAME(fkc.referenced_object_id)
 +'(' + col1.name +')'
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkc on fk.parent_object_id = fkc.parent_object_id and fk.referenced_object_id = fkc.referenced_object_id
inner join sys.tables tb on fk.parent_object_id = tb.object_id
inner join sys.columns col on fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
inner join sys.columns col1 on fkc.referenced_object_id = col1.object_id and fkc.referenced_column_id = col1.column_id
where  fk.referenced_object_id = object_id('MasterTable_name')