获取所有的外键信息

通过查询系统表,可以得到一个数据库里的所有外键信息,得到这些信息后就可以生成一些脚本,比如删除某张表的所有外键,根据现有外键信息生成新的外键.

    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
go

declare @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