不知道为什么没有人建议，但我使用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。如果存在，那张桌子就是 返回列。

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

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

这是更一般的形式:

 SELECT name
 FROM YOURDATABASE_PROD.sys.objects
 WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT' and name LIKE '%' + @tablename + '%' and
 name NOT LIKE '[a-zA-Z0-9]' + @tablename + '%' and name NOT LIKE '%' + @tablename + '[a-zA-Z0-9]' 

我正在使用这个脚本来查找与外键相关的所有细节。 我正在使用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

试试这个:

sp_help 'TableName'

这里有如何计算所选Id的所有责任。只需更改@dbTableName值，@dbRowId值及其类型(如果是int，则需要在第82行中删除“..设置@sql = ..))。享受。

DECLARE @dbTableName varchar(max) = 'User'
DECLARE @dbRowId uniqueidentifier = '21d34ecd-c1fd-11e2-8545-002219a42e1c'

DECLARE @FK_ROWCOUNT int
DECLARE @SQL nvarchar(max)

DECLARE @PKTABLE_QUALIFIER sysname
DECLARE @PKTABLE_OWNER sysname
DECLARE @PKTABLE_NAME sysname
DECLARE @PKCOLUMN_NAME sysname
DECLARE @FKTABLE_QUALIFIER sysname
DECLARE @FKTABLE_OWNER sysname
DECLARE @FKTABLE_NAME sysname
DECLARE @FKCOLUMN_NAME sysname
DECLARE @UPDATE_RULE smallint
DECLARE @DELETE_RULE smallint
DECLARE @FK_NAME sysname
DECLARE @PK_NAME sysname
DECLARE @DEFERRABILITY sysname

IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL
    DROP 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 FOR
    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 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 = @dbTableName
OPEN 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 = 0
   BEGIN
        SET @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;
GO
SELECT * FROM #Temp1
GO

通过@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;

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