这个答案建立在sp_fkeys的基础上，但格式化类似于sp_fkeys，适用于多个列并列出它们的顺序。

SELECT fk_obj.name    AS FK_NAME,
       pk_schema.name AS PKTABLE_OWNER,
       pk_table.name  AS PKTABLE_NAME,
       pk_column.name AS PKCOLUMN_NAME,
       fk_schema.name AS FKTABLE_OWNER,
       fk_table.name  AS FKTABLE_NAME,
       fk_column.name AS FKCOLUMN_NAME,
       ROW_NUMBER() over (
           PARTITION BY fk_obj.name, fk_schema.name
           ORDER BY fkc.constraint_column_id
           )          AS KEY_SEQ
FROM sys.foreign_key_columns fkc
         INNER JOIN sys.objects fk_obj
                    ON fk_obj.object_id = fkc.constraint_object_id
         INNER JOIN sys.tables fk_table
                    ON fk_table.object_id = fkc.parent_object_id
         INNER JOIN sys.schemas fk_schema
                    ON fk_table.schema_id = fk_schema.schema_id
         INNER JOIN sys.columns fk_column
                    ON fk_column.column_id = parent_column_id
                        AND fk_column.object_id = fk_table.object_id
         INNER JOIN sys.tables pk_table
                    ON pk_table.object_id = fkc.referenced_object_id
         INNER JOIN sys.schemas pk_schema
                    ON pk_table.schema_id = pk_schema.schema_id
         INNER JOIN sys.columns pk_column
                    ON pk_column.column_id = fkc.referenced_column_id
                        AND pk_column.object_id = pk_table.object_id;

最初的问题要求将所有外键的列表放入一个高度引用的表中，以便可以删除表。

这个小查询返回将所有外键放入特定表所需的“drop foreign key”命令:

SELECT 
   'ALTER TABLE ['+sch.name+'].['+referencingTable.Name+'] DROP CONSTRAINT ['+foreignKey.name+']' '[DropCommand]'
FROM sys.foreign_key_columns fk
    JOIN sys.tables referencingTable ON fk.parent_object_id = referencingTable.object_id
    JOIN sys.schemas sch ON referencingTable.schema_id = sch.schema_id
    JOIN sys.objects foreignKey ON foreignKey.object_id = fk.constraint_object_id
    JOIN sys.tables referencedTable ON fk.referenced_object_id = referencedTable.object_id
WHERE referencedTable.name = 'MyTableName'

示例输出:

[DropCommand]
ALTER TABLE [dbo].[OtherTable1] DROP CONSTRAINT [FK_OtherTable1_MyTable]
ALTER TABLE [dbo].[OtherTable2] DROP CONSTRAINT [FK_OtherTable2_MyTable]

省略where -子句以获取当前数据库中所有外键的删除命令。

SELECT
  object_name(parent_object_id),
  object_name(referenced_object_id),
  name 
FROM sys.foreign_keys
WHERE parent_object_id = object_id('Table Name')

试试这个:

sp_help 'TableName'

这会给你:

FK本身 FK所属的Schema “引用表”或者有FK的表 “引用列”或引用表中指向FK的列 “引用表”或具有FK指向的键列的表 “引用列”或者是FK指向的键的列

下面的代码:

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

SELECT
OBJECT_NAME(parent_object_id) 'Parent table',
c.NAME 'Parent column name',
OBJECT_NAME(referenced_object_id) 'Referenced table',
cref.NAME 'Referenced column name'
FROM 
sys.foreign_key_columns fkc 
INNER JOIN 
sys.columns c 
   ON fkc.parent_column_id = c.column_id 
      AND fkc.parent_object_id = c.object_id
INNER JOIN 
sys.columns cref 
   ON fkc.referenced_column_id = cref.column_id 
      AND fkc.referenced_object_id = cref.object_id  where   OBJECT_NAME(parent_object_id) = 'tablename'

如果你想获得所有表的外键关系，排除where子句，否则写你的tablename而不是tablename