第一个

EXEC sp_fkeys 'Table', 'Schema'

然后使用NimbleText处理你的结果

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

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

我正在使用这个脚本来查找与外键相关的所有细节。 我正在使用INFORMATION.SCHEMA。 下面是一个SQL脚本:

SELECT 
    ccu.table_name AS SourceTable
    ,ccu.constraint_name AS SourceConstraint
    ,ccu.column_name AS SourceColumn
    ,kcu.table_name AS TargetTable
    ,kcu.column_name AS TargetColumn
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
    INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
        ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME 
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
        ON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME  
ORDER BY ccu.table_name

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

 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)

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

SELECT t.name AS TableWithForeignKey, fk.constraint_column_id AS FK_PartNo, 
       c.name AS ForeignKeyColumn, o.name AS FK_Name 
  FROM sys.foreign_key_columns AS fk
       INNER JOIN sys.tables AS t ON fk.parent_object_id = t.object_id
       INNER JOIN sys.columns AS c ON fk.parent_object_id = c.object_id 
                                  AND fk.parent_column_id = c.column_id
       INNER JOIN sys.objects AS o ON fk.constraint_object_id = o.object_id
  WHERE fk.referenced_object_id = (SELECT object_id FROM sys.tables 
                                        WHERE name = 'TableOthersForeignKeyInto')
  ORDER BY TableWithForeignKey, FK_PartNo;

在一个查询中显示表，列和外键名称。

我在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