获取所有的外键信息

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

     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

 

转载于:https://www.cnblogs.com/doll-net/archive/2011/03/03/get_all_foreign_key_info.html