最简单的方法是使用sys。SQL中的foreign_keys_columns。这里的表包含了所有外键的对象ID，包括它们的引用列ID、引用表ID以及引用列和表。由于Id保持不变，因此对于Schema和表中的进一步修改，结果将是可靠的。

查询:

SELECT    
OBJECT_NAME(fkeys.constraint_object_id) foreign_key_name
,OBJECT_NAME(fkeys.parent_object_id) referencing_table_name
,COL_NAME(fkeys.parent_object_id, fkeys.parent_column_id) referencing_column_name
,OBJECT_SCHEMA_NAME(fkeys.parent_object_id) referencing_schema_name
,OBJECT_NAME (fkeys.referenced_object_id) referenced_table_name
,COL_NAME(fkeys.referenced_object_id, fkeys.referenced_column_id) 
referenced_column_name
,OBJECT_SCHEMA_NAME(fkeys.referenced_object_id) referenced_schema_name
FROM sys.foreign_key_columns AS fkeys

我们还可以使用where添加过滤器

WHERE OBJECT_NAME(fkeys.parent_object_id) = 'table_name' AND 
OBJECT_SCHEMA_NAME(fkeys.parent_object_id) = 'schema_name'

最简单的方法是使用sys。SQL中的foreign_keys_columns。这里的表包含了所有外键的对象ID，包括它们的引用列ID、引用表ID以及引用列和表。由于Id保持不变，因此对于Schema和表中的进一步修改，结果将是可靠的。

查询:

SELECT    
OBJECT_NAME(fkeys.constraint_object_id) foreign_key_name
,OBJECT_NAME(fkeys.parent_object_id) referencing_table_name
,COL_NAME(fkeys.parent_object_id, fkeys.parent_column_id) referencing_column_name
,OBJECT_SCHEMA_NAME(fkeys.parent_object_id) referencing_schema_name
,OBJECT_NAME (fkeys.referenced_object_id) referenced_table_name
,COL_NAME(fkeys.referenced_object_id, fkeys.referenced_column_id) 
referenced_column_name
,OBJECT_SCHEMA_NAME(fkeys.referenced_object_id) referenced_schema_name
FROM sys.foreign_key_columns AS fkeys

我们还可以使用where添加过滤器

WHERE OBJECT_NAME(fkeys.parent_object_id) = 'table_name' AND 
OBJECT_SCHEMA_NAME(fkeys.parent_object_id) = 'schema_name'

以下是我认为在SQL Server 2016中处理这种情况的最佳实践。

你必须列出外键使用:

EXEC sp_fkeys 'TableName'

在这里你可以看到fk的全部信息。注意列FKTABLE_NAME, FKCOLUMN_NAME, FK_NAME, UPDATE_RULE, DELETE_RULE是你需要删除外键并在截断后再次实现它们的信息。

你可以组织一个脚本如下:

-- EXEC sp_fkeys 'TableName'

-- DROP CONSTRAINTS: I drop one, here drop every constraint you desire. 
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TableName
    DROP CONSTRAINT IF EXISTS FK_TableName_OtherTable
GO
ALTER TABLE dbo.TableName SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

-- TRUNCATE 
BEGIN TRANSACTION
TRUNCATE TABLE TableName
GO
COMMIT


-- RECREATE CONSTRAINTS: I recreate 1, here recreate every fk you desire
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TableName SET (LOCK_ESCALATION = TABLE)
GO

ALTER TABLE dbo.TableName ADD CONSTRAINT
    FK_TableName_OtherTable FOREIGN KEY
    (
    Id_FK
    ) REFERENCES dbo.OtherTable
    (
    Id
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 
GO
COMMIT

** UPDATE_RULE和DELETE_RULE的值可以在sp_fkeys的文档中看到:

我知道这是一个很晚(非常晚)的回复，但我找到了这些简单的方法来找到所有的foreign_key_references。这是解决方案;

解决方案1:

EXEC SP_FKEYS 'MyTableName';   // It'll show you the all the information(in multiple tables) regarding to the TableName with all ForeignKey_References.

解决方案2:

EXEC SP_HELP 'MyTableName';   // It'll show all ForeignKey references in a single table.

解决方案03:

// It'll show you the Column_Name with Referenced_Table_Name

SELECT 
   COL_NAME(fc.parent_object_id,fc.parent_column_id) Column_Name,
   OBJECT_NAME(f.parent_object_id) Table_Name
FROM 
   sys.foreign_keys AS f
INNER JOIN 
   sys.foreign_key_columns AS fc 
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE 
   OBJECT_NAME (f.referenced_object_id) = 'MyTableName'

希望这对你有很大帮助。: -)

SQL Server中所有引用给定表的外键列表:

您可以通过以下查询获得引用表名和列名…

SELECT 
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM 
   sys.foreign_keys AS f
INNER JOIN 
   sys.foreign_key_columns AS fc 
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE 
   OBJECT_NAME (f.referenced_object_id) = 'TableName'

下面的截图让你理解…

上面有一些不错的答案。但我更喜欢一个问题就能得到答案。 这段代码来自sys. .Sp_helpconstraint (sys proc)

这是微软查找是否有与tbl关联的外键的方法。

--setup variables. Just change 'Customer' to tbl you want
declare @objid int,
    @objname nvarchar(776)
select @objname = 'Customer'    
select @objid = object_id(@objname)

if exists (select * from sys.foreign_keys where referenced_object_id = @objid)
    select 'Table is referenced by foreign key' =
        db_name() + '.'
        + rtrim(schema_name(ObjectProperty(parent_object_id,'schemaid')))
        + '.' + object_name(parent_object_id)
        + ': ' + object_name(object_id)
    from sys.foreign_keys 
    where referenced_object_id = @objid 
    order by 1

答案看起来像这样:test_db_name.dbo。账户:FK_Account_Customer