如何借助sql脚本查找外键约束的名称 [英] How to find the name of foreign key constraint with the help of sql Script

问题描述

借助表名，列名和引用表名，如何使用sql脚本查找外键约束的名称.

例如
表格名称:POrd
列名称:OrdRevId

OrdRevId在PordRev(OrdRevId)上具有外键约束

引用表名称:POrdRev
列名(主键):OrdRevId

解决方案

SQL脚本，用于列出当前数据库中的所有外键关系.

  SELECT 
    K_Table = FK.TABLE_NAME，
    FK_Column = CU.COLUMN_NAME，
    PK_Table = PK.TABLE_NAME，
    PK_Column = PT.COLUMN_NAME，
    Constraint_Name = C.CONSTRAINT_NAME
 FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
 INNER   JOIN  INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    打开 C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
 INNER   JOIN  INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    打开 C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
 INNER   JOIN  INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    打开 C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
 INNER   JOIN (
            选择
                i1.TABLE_NAME，
                i2.COLUMN_NAME
             FROM 
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
             INNER   JOIN  INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                打开 i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            位置
                i1.CONSTRAINT_TYPE = ' 主键'
           )PT
    打开 PT.TABLE_NAME = PK.TABLE_NAME 

同时，您还可以在SQL Server Management Studio的数据库关系图中以图形方式查看关系.

选中此选项:

  AS  ForeignKey，
   对象名称(f.parent_object_id) AS  TableName，
    COL_NAME (fc.parent_object_id，fc.parent_column_id) AS  ColumnName，
   对象名称(f.referenced_object_id) AS  ReferenceTableName，
    COL_NAME (fc.referenced_object_id，fc.referenced_column_id) AS  ReferenceColumnName
 FROM  sys.foreign_keys  AS  f
 INNER   JOIN  sys.foreign_key_columns  AS  fc
   打开 f. OBJECT_ID  = fc.constraint_object_id 

Hi,
With the help of table name,column name and referenced table name, How to find the name of foreign key constraint using sql script.

Ex.
Table Name: POrd
Column Name: OrdRevId

OrdRevId has a foreign Key constraint on PordRev(OrdRevId)

Referenced table name :POrdRev
column name(Primary Key): OrdRevId

解决方案

SQL Script to list all Foreign Key Relationships within the current database.

SELECT
    K_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
            SELECT
                i1.TABLE_NAME,
                i2.COLUMN_NAME
            FROM
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE
                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME

At the sametime, you can also view relationships graphically within SQL Server Management studio within Database Diagrams.

Check this one:

SELECT f.name AS ForeignKey, 
   OBJECT_NAME(f.parent_object_id) AS TableName, 
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, 
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, 
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName 
FROM sys.foreign_keys AS f 
INNER JOIN sys.foreign_key_columns AS fc 
   ON f.OBJECT_ID = fc.constraint_object_id

这篇关于如何借助sql脚本查找外键约束的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！