获取所有的外键信息

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

    
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