sp_drop_fkeys can be used to drop all, or some, of the foreign keys referencing one or more tables.
sp_drop_fkeys [@parent_tb_name = prntname]
[, @child_tb_name = chldname]
[, @fk_cns_name = consname]
[,@diagnostics_option = diagopt]
[,@num_targeted_fkeys= @ScriptVar OUTPUT]
where:
prntname
Is the name of the parent table for which the foreign keys will be dropped. If prntname is a Transact-SQL wildcard pattern, the foreign keys for all tables whose names match the pattern will be dropped. Prntname is a required parameter.
chldname
Is the name of the referencing table whose references constraints will be dropped. If chldname is a Transact-SQL wildcard pattern, the references constraints referring back to prntname from all child tables whose names match the pattern will be dropped.
consname
Is the name of the references constraint to be dropped. If consname is a Transact-SQL wildcard pattern, all references constraints whose name matches the pattern will be dropped.
diagopt
Controls the production of a diagnostic report. The values are 'yes', 'no', or 'only'; 'yes' is the default if diagopt is not specified.
@num_targeted_fkeys= @ScriptVar OUTPUT
This parameter returns the number of constraints affected by each execution of sp_drop_fkeys. @ScriptVar should be declared as an integer.
If prntname is the name of a table, then all the references constraints which refer back to that table will be dropped. If chldname or consname are also specified, then only the subset of references constraints that qualify for all three parameters will be dropped. If prntname is a LIKE wildcard pattern, all tables whose names match the pattern will have their references constraints dropped.
If chldname is the name of a table, all of its references constraints back to the table or tables named in prntname are dropped. If chldname is a LIKE wildcard pattern, all tables whose names match the pattern will have their constraints dropped.
If consname is the name of a constraint, that constraint will be dropped. If consname is a LIKE wildcard pattern, then all constraints whose names match the pattern will be dropped.
If diagopt is 'yes', all qualifying constraints are dropped and a diagnostic report indicating which constraints were dropped is produced. If diagopt is 'no', the constraints will be dropped without a report. If diagopt is 'only', then no constraints are dropped, and only the diagnostic report is produced.
To drop all the references constraints that refer back to a specific table:
sp_drop_fkeys PriTab
To drop all the references constrains on tables whose name contain a specific string and refer back to a specific parent table:
sp_drop_fkeys PriTab, '%RefTab%'
To drop a specific constraint that refers back to a specific table:
sp_drop_fkeys @parent_tb_name='PriTab', @fk_cns_name='RefCon1'
To report how many constraints would be dropped on all child tables whose names match a string, and also to retrieve the count of constraints affected:
declare @OutKeyCount int
declare @Msg char(50)
exec sp_drop_fkeys @parent_tb_name='PriTab',
@child_tb_name='RefTab%',
@diagnostics_option = 'ONLY',
@num_targeted_fkeys = @OutKeyCount OUTPUT
select @Msg = 'Output keycount = ' + convert(char(3), @OutKeyCount)
print @Msg