通过查询系统表,可以得到一个数据库里的所有外键信息,得到这些信息后就可以生成一些脚本,比如删除某张表的所有外键,根据现有外键信息生成新的外键.
select fk.name fkname,constable.name constablename,conscol.name conscolname,reftable.name reftablename,refcol.name refcolname
from sys.foreign_keys fk
join sys.objects constable on fk.parent_object_id=constable.object_id
join sys.objects reftable on fk.referenced_object_id=reftable.object_id
join sys.foreign_key_columns fkc on fkc.constraint_object_id=fk.object_id
join sys.columns conscol on fkc.parent_column_id =conscol.column_id and fkc.parent_object_id=conscol.object_id
join sys.columns refcol on fkc.referenced_column_id=refcol.column_id and fkc.referenced_object_id=refcol.object_id
以上查询得到外键名称,外键基表,外键列,外键引用表,外键引用列.
以下语句删除数据库db里关于tblname的外键:
use db
godeclare @references_name nvarchar(100),
@table_name nvarchar(100)declare cursor_references cursor for
select fk.name fkname,constable.name constable from sys.foreign_keys fk
join sys.objects constable on fk.parent_object_id=constable.object_id
join sys.objects reftable on fk.referenced_object_id=reftable.object_id
join sys.foreign_key_columns fkc on fkc.constraint_object_id=fk.object_id
join sys.columns conscol on fkc.parent_column_id =conscol.column_id and fkc.parent_object_id=conscol.object_id
join sys.columns refcol on fkc.referenced_column_id=refcol.column_id and fkc.referenced_object_id=refcol.object_id
where reftable.name='tblname'
open cursor_references
fetch next from cursor_references into @references_name,@table_name
while @@fetch_status = 0
begin
exec('alter table '+@table_name+' drop constraint '+@references_name)
fetch next from cursor_references into @references_name,@table_name
end
close cursor_references
deallocate cursor_references
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。