我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?

(SQL比在管理工作室的GUI中点击更可取)


当前回答

Mysql服务器有information_schema。REFERENTIAL_CONSTRAINTS表供参考,您可以通过表名或引用表名过滤它。

其他回答

我知道这是一个很晚(非常晚)的回复,但我找到了这些简单的方法来找到所有的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 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的文档中看到:

不知道为什么没有人建议,但我使用sp_fkeys查询给定表的外键:

EXEC sp_fkeys 'TableName'

你也可以指定模式:

EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'

在没有指定模式的情况下,文档声明如下:

如果没有指定pktable_owner,则默认表可见性规则 的基础DBMS应用程序。 在SQL Server中,如果当前用户拥有一个具有指定 Name,返回该表的列。如果pktable_owner不是 且当前用户不拥有具有指定属性的表 Pktable_name时,该过程查找具有指定 由数据库所有者拥有的Pktable_name。如果存在,那张桌子就是 返回列。

以下的解决方案对我来说很有效:

--Eliminar las llaves foraneas
declare @query varchar(8000)
declare cursorRecorrerTabla cursor for

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


--3ro. abrir el cursor.
open cursorRecorrerTabla
fetch next from cursorRecorrerTabla
into @query
while @@fetch_status = 0
begin
--inicio cuerpo del cursor
    print @query
    exec(@query)
--fin cuerpo del cursor
fetch next from cursorRecorrerTabla
into @query
end
--cerrar cursor
close cursorRecorrerTabla
deallocate cursorRecorrerTabla

你可以通过以下查询找到:

 SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table', 
      OBJECT_NAME(FK.parent_object_id) 'Referring Table', FK.name 'Foreign Key', 
      COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
      COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
     FROM sys.foreign_keys AS FK
             INNER JOIN sys.foreign_key_columns AS FKC 
                 ON FKC.constraint_object_id = FK.OBJECT_ID
     WHERE OBJECT_NAME (FK.referenced_object_id) = 'YourTableName'
     AND COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) = 'YourColumnName'
     order by  OBJECT_NAME(FK.parent_object_id)