如何在SQLServer中列出引用给定表的所有外键？

我需要删除SQL服务器数据库中高度引用的表。如何获得删除表所需删除的所有外键约束的列表？

（SQL答案比在管理工作室的GUI中点击更可取。

863462 次浏览
小开

试试这个：

sp_help 'TableName'
小开

我会在SQLServer Management Studio中使用数据库图表功能，但由于您排除了这一点-这在SQLServer 2008（没有2005）中为我工作。

要获取引用表和列名的列表…

selectt.name as TableWithForeignKey,fk.constraint_column_id as FK_PartNo, c.name as ForeignKeyColumnfromsys.foreign_key_columns as fkinner joinsys.tables as t on fk.parent_object_id = t.object_idinner joinsys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_idwherefk.referenced_object_id = (select object_idfrom sys.tableswhere name = 'TableOthersForeignKeyInto')order byTableWithForeignKey, FK_PartNo

获取外键约束的名称

select distinct name from sys.objects where object_id in(   select fk.constraint_object_id from sys.foreign_key_columns as fkwhere fk.referenced_object_id =(select object_id from sys.tables where name = 'TableOthersForeignKeyInto'))
小开 
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), sysconv(smallint,0)),UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade')WHEN 1 THEN 0ELSE 1END),DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade')WHEN 1 THEN 0ELSE 1END),FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),PK_NAME = CONVERT(SYSNAME,I.NAME),DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLEFROM   SYS.ALL_OBJECTS O1,SYS.ALL_OBJECTS O2,SYS.ALL_COLUMNS C1,SYS.ALL_COLUMNS C2,SYS.FOREIGN_KEYS FINNER JOIN SYS.FOREIGN_KEY_COLUMNS KON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)INNER JOIN SYS.INDEXES ION (F.REFERENCED_OBJECT_ID = I.OBJECT_IDAND F.KEY_INDEX_ID = I.INDEX_ID)WHERE  O1.OBJECT_ID = F.REFERENCED_OBJECT_IDAND O2.OBJECT_ID = F.PARENT_OBJECT_IDAND C1.OBJECT_ID = F.REFERENCED_OBJECT_IDAND C2.OBJECT_ID = F.PARENT_OBJECT_IDAND C1.COLUMN_ID = K.REFERENCED_COLUMN_IDAND C2.COLUMN_ID = K.PARENT_COLUMN_ID
小开

通过@Gishu所做的工作，我能够在SQLServer 2005中生成和使用以下SQL 

SELECT t.name AS TableWithForeignKey, fk.constraint_column_id AS FK_PartNo,c.name AS ForeignKeyColumn, o.name AS FK_NameFROM sys.foreign_key_columns AS fkINNER JOIN sys.tables AS t ON fk.parent_object_id = t.object_idINNER JOIN sys.columns AS c ON fk.parent_object_id = c.object_idAND fk.parent_column_id = c.column_idINNER JOIN sys.objects AS o ON fk.constraint_object_id = o.object_idWHERE fk.referenced_object_id = (SELECT object_id FROM sys.tablesWHERE name = 'TableOthersForeignKeyInto')ORDER BY TableWithForeignKey, FK_PartNo;

它在1个查询中显示所有表、列和外键名称。

小开 
SELECTobject_name(parent_object_id),object_name(referenced_object_id),nameFROM sys.foreign_keysWHERE parent_object_id = object_id('Table Name')
小开 
 SELECT OBJECT_NAME(fk.parent_object_id) as ReferencingTable,OBJECT_NAME(fk.constraint_object_id) as [FKContraint]FROM sys.foreign_key_columns as fkWHERE fk.referenced_object_id = OBJECT_ID('ReferencedTable', 'U')

这仅显示外键约束的关系。我的数据库显然早于FK约束。一些表使用触发器来强制引用完整性，有时除了一个类似命名的列之外什么也没有来指示关系（根本没有引用完整性）。

幸运的是，我们确实有一个一致的命名场景，所以我能够找到引用表这样的观点：

SELECT OBJECT_NAME(object_id) from sys.columns where name like 'client_id'

我使用这个选择作为生成脚本的基础，它做了我需要做的事情相关表格

小开

上面的一些好答案。但我更喜欢用一个查询的答案。这段代码取自sys.sp_helpconstraint（sys proc）

如果有与tbl关联的外键，Microsoft会以这种方式进行查找。 

--setup variables. Just change 'Customer' to tbl you wantdeclare @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_keyswhere referenced_object_id = @objidorder by 1

答案如下：test_db_name.dbo.帐户：FK_Account_Customer

小开
最佳答案

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

EXEC sp_fkeys 'TableName'

您还可以指定模式：

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

在不指定架构的情况下，文档状态如下：

如果未指定pktable_owner，则默认表可见性规则的底层DBMS应用。

在SQL服务器中，如果当前用户拥有一个具有指定name，则返回该表的列。如果pktable_owner不是指定并且当前用户不拥有具有指定pktable_name，该过程查找具有指定pktable_name数据库所有者拥有的。如果存在，则该表返回列。

小开

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

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

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

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

select O.name as [Object_Name], C.text as [Object_Definition]from sys.syscomments Cinner join sys.all_objects O ON C.id = O.object_idwhere C.text like '%table_name%'
小开

确定数据库中所有表的主键和唯一键…

这应该列出所有的限制，最后你可以把你的过滤器

/* CAST IS DONE , SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT*/WITH   ALL_KEYS_IN_TABLE (CONSTRAINT_NAME,CONSTRAINT_TYPE,PARENT_TABLE_NAME,PARENT_COL_NAME,PARENT_COL_NAME_DATA_TYPE,REFERENCE_TABLE_NAME,REFERENCE_COL_NAME)AS(SELECT  CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)) ,CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)) ,PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)) ,PARENT_COL_NAME=CAST ( PKnUKEYCol.name AS VARCHAR(30)) ,PARENT_COL_NAME_DATA_TYPE=  oParentColDtl.DATA_TYPE,REFERENCE_TABLE_NAME='' ,REFERENCE_COL_NAME=''
FROM sys.key_constraints as PKnUKEYINNER JOIN sys.tables as PKnUTableON PKnUTable.object_id = PKnUKEY.parent_object_idINNER JOIN sys.index_columns as PKnUColIdxON PKnUColIdx.object_id = PKnUTable.object_idAND PKnUColIdx.index_id = PKnUKEY.unique_index_idINNER JOIN sys.columns as PKnUKEYColON PKnUKEYCol.object_id = PKnUTable.object_idAND PKnUKEYCol.column_id = PKnUColIdx.column_idINNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtlON oParentColDtl.TABLE_NAME=PKnUTable.nameAND oParentColDtl.COLUMN_NAME=PKnUKEYCol.nameUNION ALLSELECT  CONSTRAINT_NAME= CAST (oConstraint.name AS VARCHAR(30)) ,CONSTRAINT_TYPE='FK',PARENT_TABLE_NAME=CAST (oParent.name AS VARCHAR(30)) ,PARENT_COL_NAME=CAST ( oParentCol.name AS VARCHAR(30)) ,PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,REFERENCE_TABLE_NAME=CAST ( oReference.name AS VARCHAR(30)) ,REFERENCE_COL_NAME=CAST (oReferenceCol.name AS VARCHAR(30))FROM sys.foreign_key_columns FKCINNER JOIN sys.sysobjects oConstraintON FKC.constraint_object_id=oConstraint.idINNER JOIN sys.sysobjects oParentON FKC.parent_object_id=oParent.idINNER JOIN sys.all_columns oParentColON FKC.parent_object_id=oParentCol.object_id /* ID of the object to which this column belongs.*/AND FKC.parent_column_id=oParentCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/INNER JOIN sys.sysobjects oReferenceON FKC.referenced_object_id=oReference.idINNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtlON oParentColDtl.TABLE_NAME=oParent.nameAND oParentColDtl.COLUMN_NAME=oParentCol.nameINNER JOIN sys.all_columns oReferenceColON FKC.referenced_object_id=oReferenceCol.object_id /* ID of the object to which this column belongs.*/AND FKC.referenced_column_id=oReferenceCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
)
select * from   ALL_KEYS_IN_TABLEwherePARENT_TABLE_NAME  in ('YOUR_TABLE_NAME')or REFERENCE_TABLE_NAME  in ('YOUR_TABLE_NAME')ORDER BY PARENT_TABLE_NAME,CONSTRAINT_NAME;

如需参考，请阅读通过-http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx

小开 
SELECTOBJECT_NAME(parent_object_id) 'Parent table',c.NAME 'Parent column name',OBJECT_NAME(referenced_object_id) 'Referenced table',cref.NAME 'Referenced column name'FROMsys.foreign_key_columns fkcINNER JOINsys.columns cON fkc.parent_column_id = c.column_idAND fkc.parent_object_id = c.object_idINNER JOINsys.columns crefON fkc.referenced_column_id = cref.column_idAND fkc.referenced_object_id = cref.object_id  where   OBJECT_NAME(parent_object_id) = 'tablename'

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

小开

这给你：

  • FK本身
  • FK所属的架构
  • “引用表”或具有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 fkcINNER JOIN sys.objects objON obj.object_id = fkc.constraint_object_idINNER JOIN sys.tables tab1ON tab1.object_id = fkc.parent_object_idINNER JOIN sys.schemas schON tab1.schema_id = sch.schema_idINNER JOIN sys.columns col1ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_idINNER JOIN sys.tables tab2ON tab2.object_id = fkc.referenced_object_idINNER JOIN sys.columns col2ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
小开

这获取涉及所选表的任何外键。*假设_FIRSTABLENAME_SECONDTABLENAME格式。

 declare @tablename as varchar(MAX)SET @tablename = 'yourtablename'SELECT nameFROM YOURDATABASE.sys.objectsWHERE type_desc = 'FOREIGN_KEY_CONSTRAINT' and (name LIKE '%_' + @tablename + 'empdb_%' or name LIKE '%_' + @tablename )

这是一个更一般的形式：

 SELECT nameFROM YOURDATABASE_PROD.sys.objectsWHERE type_desc = 'FOREIGN_KEY_CONSTRAINT' and name LIKE '%' + @tablename + '%' andname NOT LIKE '[a-zA-Z0-9]' + @tablename + '%' and name NOT LIKE '%' + @tablename + '[a-zA-Z0-9]'
小开

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

这个小查询返回将所有外键放到特定表中所需的所有“删除外键”命令：

SELECT'ALTER TABLE ['+sch.name+'].['+referencingTable.Name+'] DROP CONSTRAINT ['+foreignKey.name+']' '[DropCommand]'FROM sys.foreign_key_columns fkJOIN sys.tables referencingTable ON fk.parent_object_id = referencingTable.object_idJOIN sys.schemas sch ON referencingTable.schema_id = sch.schema_idJOIN sys.objects foreignKey ON foreignKey.object_id = fk.constraint_object_idJOIN sys.tables referencedTable ON fk.referenced_object_id = referencedTable.object_idWHERE referencedTable.name = 'MyTableName'

输出示例：

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

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

小开

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

-->>SPECIFY THE DESIRED DBUSE ???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 0ELSE 1END),DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.object_id,'CnstIsDeleteCascade')WHEN 1 THEN 0ELSE 1END),FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.object_id)),PK_NAME = CONVERT(SYSNAME,I.name),DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLEFROM   sys.all_objects O1,sys.all_objects O2,sys.all_columns C1,sys.all_columns C2,sys.foreign_keys FINNER JOIN sys.foreign_key_columns KON (K.constraint_object_id = F.object_id)INNER JOIN sys.indexes ION (F.referenced_object_id = I.object_idAND F.key_index_id = I.index_id)WHERE  O1.object_id = F.referenced_object_idAND O2.object_id = F.parent_object_idAND C1.object_id = F.referenced_object_idAND C2.object_id = F.parent_object_idAND C1.column_id = K.referenced_column_idAND C2.column_id = K.parent_column_idAND (   O1.name = @tblNameOR O2.name = @tblNameOR @tblName IS null)ORDER BY PKTABLE_NAME,FKTABLE_NAME
小开

我正在使用此脚本查找与外键相关的所有详细信息。我用INFORMATION.SCHEMA.下面是一个SQL脚本：

SELECTccu.table_name AS SourceTable,ccu.constraint_name AS SourceConstraint,ccu.column_name AS SourceColumn,kcu.table_name AS TargetTable,kcu.column_name AS TargetColumnFROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccuINNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rcON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAMEINNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcuON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAMEORDER BY ccu.table_name
小开

有如何获得所选ID的所有职责计数。只需更改@dbTableName值、@dbRowId值及其类型（如果是int，您需要删除第82行中的"（… SET@SQL=…）。享受。

DECLARE @dbTableName varchar(max) = 'User'DECLARE @dbRowId uniqueidentifier = '21d34ecd-c1fd-11e2-8545-002219a42e1c'
DECLARE @FK_ROWCOUNT intDECLARE @SQL nvarchar(max)
DECLARE @PKTABLE_QUALIFIER sysnameDECLARE @PKTABLE_OWNER sysnameDECLARE @PKTABLE_NAME sysnameDECLARE @PKCOLUMN_NAME sysnameDECLARE @FKTABLE_QUALIFIER sysnameDECLARE @FKTABLE_OWNER sysnameDECLARE @FKTABLE_NAME sysnameDECLARE @FKCOLUMN_NAME sysnameDECLARE @UPDATE_RULE smallintDECLARE @DELETE_RULE smallintDECLARE @FK_NAME sysnameDECLARE @PK_NAME sysnameDECLARE @DEFERRABILITY sysname
IF OBJECT_ID('tempdb..#Temp1') IS NOT NULLDROP TABLE #Temp1;CREATE TABLE #Temp1 (PKTABLE_QUALIFIER sysname,PKTABLE_OWNER sysname,PKTABLE_NAME sysname,PKCOLUMN_NAME sysname,FKTABLE_QUALIFIER sysname,FKTABLE_OWNER sysname,FKTABLE_NAME sysname,FKCOLUMN_NAME sysname,UPDATE_RULE smallint,DELETE_RULE smallint,FK_NAME sysname,PK_NAME sysname,DEFERRABILITY sysname,FK_ROWCOUNT int);DECLARE FK_Counter_Cursor CURSOR FORSELECT 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), sysconv(smallint,0)),UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade')WHEN 1 THEN 0ELSE 1END),DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade')WHEN 1 THEN 0ELSE 1END),FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),PK_NAME = CONVERT(SYSNAME,I.NAME),DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLEFROM   SYS.ALL_OBJECTS O1,SYS.ALL_OBJECTS O2,SYS.ALL_COLUMNS C1,SYS.ALL_COLUMNS C2,SYS.FOREIGN_KEYS FINNER JOIN SYS.FOREIGN_KEY_COLUMNS KON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)INNER JOIN SYS.INDEXES ION (F.REFERENCED_OBJECT_ID = I.OBJECT_IDAND F.KEY_INDEX_ID = I.INDEX_ID)WHERE  O1.OBJECT_ID = F.REFERENCED_OBJECT_IDAND O2.OBJECT_ID = F.PARENT_OBJECT_IDAND C1.OBJECT_ID = F.REFERENCED_OBJECT_IDAND C2.OBJECT_ID = F.PARENT_OBJECT_IDAND C1.COLUMN_ID = K.REFERENCED_COLUMN_IDAND C2.COLUMN_ID = K.PARENT_COLUMN_IDAND O1.NAME = @dbTableNameOPEN FK_Counter_Cursor;FETCH NEXT FROM FK_Counter_Cursor INTO @PKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY;WHILE @@FETCH_STATUS = 0BEGINSET @SQL = 'SELECT @dbCountOut = COUNT(*) FROM [' + @FKTABLE_NAME + '] WHERE [' + @FKCOLUMN_NAME + '] = ''' + CAST(@dbRowId AS varchar(max)) + '''';EXECUTE sp_executesql @SQL, N'@dbCountOut int OUTPUT', @dbCountOut = @FK_ROWCOUNT OUTPUT;INSERT INTO #Temp1 (PKTABLE_QUALIFIER, PKTABLE_OWNER, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, FKCOLUMN_NAME, UPDATE_RULE, DELETE_RULE, FK_NAME, PK_NAME, DEFERRABILITY, FK_ROWCOUNT) VALUES (@FKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY, @FK_ROWCOUNT)FETCH NEXT FROM FK_Counter_Cursor INTO @PKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY;END;CLOSE FK_Counter_Cursor;DEALLOCATE FK_Counter_Cursor;GOSELECT * FROM #Temp1GO
小开

这是我使用的SQL代码。

SELECTf.name AS 'Name of Foreign Key',OBJECT_NAME(f.parent_object_id) AS 'Table name',COL_NAME(fc.parent_object_id,fc.parent_column_id) AS 'Fieldname',OBJECT_NAME(t.object_id) AS 'References Table name',COL_NAME(t.object_id,fc.referenced_column_id) AS 'References fieldname',
'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id) + ']  DROP CONSTRAINT [' + f.name + ']' AS 'Delete foreign key',
'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id) + ']  WITH NOCHECK ADD CONSTRAINT [' +f.name + '] FOREIGN KEY([' + COL_NAME(fc.parent_object_id,fc.parent_column_id) + ']) REFERENCES ' +'[' + OBJECT_NAME(t.object_id) + '] ([' +COL_NAME(t.object_id,fc.referenced_column_id) + '])' AS 'Create foreign key'-- , delete_referential_action_desc AS 'UsesCascadeDelete'FROM sys.foreign_keys AS f,sys.foreign_key_columns AS fc,sys.tables tWHERE f.OBJECT_ID = fc.constraint_object_idAND t.OBJECT_ID = fc.referenced_object_idAND OBJECT_NAME(t.object_id) = 'Employees'      --  Just show the FKs which reference a particular tableORDER BY 2

SQL不是特别清楚，让我们看一个例子。

因此，假设我想删除Microsoft心爱的Northwind数据库中的Employees表，但SQLServer告诉我一个或多个外键阻止了我这样做。

上面的SQL命令将返回这些结果…

外国凯斯

它显示有3个外键引用了Employees表。换句话说，在这三个外键首先被删除之前，我不允许删除（删除）这个表。

在结果中，第一行是以下外键约束在结果中的显示方式。

ALTER TABLE [dbo].[Employees]  WITH NOCHECKADD CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo])REFERENCES [dbo].[Employees] ([EmployeeID])

倒数第二列显示了我需要使用删除这些外键之一的SQL命令，例如：

ALTER TABLE [Employees] DROP CONSTRAINT [FK_Employees_Employees]

…右边的栏显示了创建的SQL…

ALTER TABLE [Employees] WITH NOCHECKADD CONSTRAINT [FK_Employees_Employees]FOREIGN KEY([ReportsTo]) REFERENCES [Employees] ([EmployeeID])

使用所有这些命令，您拥有删除相关外键所需的一切，以允许您删除表，然后稍后重新创建它们。

希望这能帮上忙。

小开

第一

EXEC sp_fkeys 'Table', 'Schema'

然后使用NimbleText来玩你的结果

小开

以下解决方案为我工作：

--Eliminar las llaves foraneasdeclare @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 fkJOIN PoaComFinH.sys.tables referencingTable ON fk.parent_object_id = referencingTable.object_idJOIN PoaComFinH.sys.schemas sch ON referencingTable.schema_id = sch.schema_idJOIN PoaComFinH.sys.objects foreignKey ON foreignKey.object_id = fk.constraint_object_idJOIN PoaComFinH.sys.tables referencedTable ON fk.referenced_object_id = referencedTable.object_id

--3ro. abrir el cursor.open cursorRecorrerTablafetch next from cursorRecorrerTablainto @querywhile @@fetch_status = 0begin--inicio cuerpo del cursorprint @queryexec(@query)--fin cuerpo del cursorfetch next from cursorRecorrerTablainto @queryend--cerrar cursorclose cursorRecorrerTabladeallocate cursorRecorrerTabla
小开

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

查询： 

SELECTOBJECT_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_nameFROM sys.foreign_key_columns AS fkeys

我们也可以通过使用“在哪里”来添加过滤器

WHERE OBJECT_NAME(fkeys.parent_object_id) = 'table_name' ANDOBJECT_SCHEMA_NAME(fkeys.parent_object_id) = 'schema_name'
小开

MySQL服务器有information_schema.REFERENTIAL_CONSTRAINTS表FYI，您可以通过表名或引用的表名对其进行过滤。

小开

您可以通过下面的查询找到： 

 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 FKINNER JOIN sys.foreign_key_columns AS FKCON FKC.constraint_object_id = FK.OBJECT_IDWHERE 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)
小开

也试试。

EXEC sp_fkeys 'tableName', 'schemaName'

使用sp_fkeys，您不仅可以通过pk表名和模式过滤结果，还可以通过fk表名和模式过滤结果。链接

小开

最佳答案@BankZ 

sp_help 'TableName'

此外，对于不同的模式

sp_help 'schemaName.TableName'
小开

SQLServer中引用给定表的所有外键列表：

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

SELECTOBJECT_NAME(f.parent_object_id) TableName,COL_NAME(fc.parent_object_id,fc.parent_column_id) ColNameFROMsys.foreign_keys AS fINNER JOINsys.foreign_key_columns AS fcON f.OBJECT_ID = fc.constraint_object_idINNER JOINsys.tables tON t.OBJECT_ID = fc.referenced_object_idWHEREOBJECT_NAME (f.referenced_object_id) = 'TableName'

以下截图供您理解…

在此处输入图片描述

小开 
with tab_list as (select t.name AS Table_Name, t.object_id, s.name AS Table_Schema  from sys.tables t, sys.schemas swhere t.schema_id = s.schema_idand s.name = 'your schema')select IIF(col.column_id = 1, tab.TABLE_SCHEMA + '.' + tab.TABLE_NAME, NULL) Table_Name,col.Name AS Column_Name, IIF(col.IS_NULLABLE= 0, 'NOT NULL', '') Nullable, st.name Type,CASE WHEN st.name = 'decimal' THEN CONVERT(NVARCHAR(4000), col.Precision) + ',' + CONVERT(NVARCHAR(4000), col.Scale)WHEN col.max_length = -1 THEN 'max'WHEN st.name in ('int', 'bit', 'bigint', 'datetime2') THEN NULLELSE CONVERT(NVARCHAR(4000), col.max_length / 2)ENDAS Length,ss.name + '.' + stab.name Referenced_Table, scol.name Referenced_Columnfrom sys.COLUMNS colINNER JOIN tab_list tab ON col.object_id = tab.object_idINNER JOIN sys.types st ON col.system_type_id = st.system_type_id AND col.user_type_id = st.user_type_idLEFT JOIN [sys].[foreign_key_columns] sfkc ON col.object_id = sfkc.parent_object_id AND col.column_id = sfkc.parent_column_idLEFT JOIN sys.tables stab ON sfkc.referenced_object_id = stab.object_idLEFT JOIN sys.columns scol ON sfkc.referenced_object_id = scol.object_id AND sfkc.referenced_column_id = scol.column_idLEFT JOIN sys.schemas ss ON ss.schema_id = stab.schema_id
小开

这个答案构建了在，但格式类似于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.nameORDER BY fkc.constraint_column_id)          AS KEY_SEQFROM sys.foreign_key_columns fkcINNER JOIN sys.objects fk_objON fk_obj.object_id = fkc.constraint_object_idINNER JOIN sys.tables fk_tableON fk_table.object_id = fkc.parent_object_idINNER JOIN sys.schemas fk_schemaON fk_table.schema_id = fk_schema.schema_idINNER JOIN sys.columns fk_columnON fk_column.column_id = parent_column_idAND fk_column.object_id = fk_table.object_idINNER JOIN sys.tables pk_tableON pk_table.object_id = fkc.referenced_object_idINNER JOIN sys.schemas pk_schemaON pk_table.schema_id = pk_schema.schema_idINNER JOIN sys.columns pk_columnON pk_column.column_id = fkc.referenced_column_idAND pk_column.object_id = pk_table.object_id;
小开

OracleSQL

select *fromall_constraintswherer_constraint_name in(select       constraint_namefromall_constraintswheretable_name='PUT_THE_TABLE_NAME_HERE');

all_constraints是Oracle DB中的一个内部表名。

小开

这是我认为在SQLServer 2016中处理此方案的最佳实践。

您必须使用以下命令列出外键：

EXEC sp_fkeys 'TableName'

在那里您可以看到FK的完整信息。请注意，列FKTABLE_NAMEFKCOLUMN_NAMEFK_NAMEUPDATE_RULEDELETE_RULE是删除外键并在截断后再次实现它们所需的信息。

您可以按如下方式组织脚本：

-- EXEC sp_fkeys 'TableName'
-- DROP CONSTRAINTS: I drop one, here drop every constraint you desire.BEGIN TRANSACTIONGOALTER TABLE dbo.TableNameDROP CONSTRAINT IF EXISTS FK_TableName_OtherTableGOALTER TABLE dbo.TableName SET (LOCK_ESCALATION = TABLE)GOCOMMIT
-- TRUNCATEBEGIN TRANSACTIONTRUNCATE TABLE TableNameGOCOMMIT

-- RECREATE CONSTRAINTS: I recreate 1, here recreate every fk you desireBEGIN TRANSACTIONGOALTER TABLE dbo.TableName SET (LOCK_ESCALATION = TABLE)GO
ALTER TABLE dbo.TableName ADD CONSTRAINTFK_TableName_OtherTable FOREIGN KEY(Id_FK) REFERENCES dbo.OtherTable(Id) ON UPDATE  NO ACTIONON DELETE  NO ACTIONGOCOMMIT

**UPDATE_RULE和DELETE_RULE的值可以在留档sp_fkeys中看到：sp_fkeys留档UPDATE_RULE和DELETE_RULE值


提交答案