以下是我认为在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的文档中看到:

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

最简单的方法是使用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'

您还应该注意对其他对象的引用。

如果表被其他表高度引用，那么它也可能被其他对象(如视图、存储过程、函数等)高度引用。

我真的推荐GUI工具，如SSMS中的“查看依赖关系”对话框或免费工具，如ApexSQL Search，因为如果你只想用SQL来搜索其他对象的依赖关系，可能会出错。

如果SQL是唯一的选择，您可以尝试这样做。

select O.name as [Object_Name], C.text as [Object_Definition]
from sys.syscomments C
inner join sys.all_objects O ON C.id = O.object_id
where C.text like '%table_name%'

我在2008年及以后一直在使用它。它类似于列出的其他一些解决方案，但字段名称是适当的大小写，以处理特定于大小写的(LatBin)排序。此外，您可以为它提供一个表名，并检索该表的信息。

-->>SPECIFY THE DESIRED DB
USE ???
GO

/*********************************************************************************************

    LIST OUT ALL PRIMARY AND FOREIGN KEY CONSTRAINTS IN A DB OR FOR A SPECIFIED TABLE

*********************************************************************************************/
DECLARE @tblName VARCHAR(255) 

/*******************/

    SET @tblName = NULL-->NULL will return all PK/FK constraints for every table in the database

/*******************/

SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()), 
       PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.schema_id)), 
       PKTABLE_NAME = CONVERT(SYSNAME,O1.name), 
       PKCOLUMN_NAME = CONVERT(SYSNAME,C1.name), 
       FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()), 
       FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.schema_id)), 
       FKTABLE_NAME = CONVERT(SYSNAME,O2.name), 
       FKCOLUMN_NAME = CONVERT(SYSNAME,C2.name), 
       -- Force the column to be non-nullable (see SQL BU 325751) 
       KEY_SEQ             = isnull(convert(smallint,K.constraint_column_id),0), 
       UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.object_id,'CnstIsUpdateCascade')  
                                        WHEN 1 THEN 0 
                                        ELSE 1 
                                      END), 
       DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.object_id,'CnstIsDeleteCascade')  
                                        WHEN 1 THEN 0 
                                        ELSE 1 
                                      END), 
       FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.object_id)), 
       PK_NAME = CONVERT(SYSNAME,I.name), 
       DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLE 
FROM   sys.all_objects O1, 
       sys.all_objects O2, 
       sys.all_columns C1, 
       sys.all_columns C2, 
       sys.foreign_keys F 
       INNER JOIN sys.foreign_key_columns K 
         ON (K.constraint_object_id = F.object_id) 
       INNER JOIN sys.indexes I 
         ON (F.referenced_object_id = I.object_id 
             AND F.key_index_id = I.index_id) 
WHERE  O1.object_id = F.referenced_object_id 
       AND O2.object_id = F.parent_object_id 
       AND C1.object_id = F.referenced_object_id 
       AND C2.object_id = F.parent_object_id 
       AND C1.column_id = K.referenced_column_id
       AND C2.column_id = K.parent_column_id
       AND (   O1.name = @tblName 
            OR O2.name = @tblName
            OR @tblName IS null)
ORDER BY PKTABLE_NAME,FKTABLE_NAME

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

这个小查询返回将所有外键放入特定表所需的“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 -子句以获取当前数据库中所有外键的删除命令。