sql - 如何检查外键是否存在？

Question

这个问题在这里已经有了答案:





SQL Script to find Foreign keys to a specific table?

(2 个回答)


5年前关闭。




我想在表中找到一个外键，但有一些更改可以重命名/更改主键。如何确定表中的外键和主键？

相关代码:

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
         WHERE CONSTRAINT_NAME = 'FK_Name'

Answer 1

随意使用这个脚本，它会返回一个包含以下值的列表:

FKName  ParentTable  ParentColumnName    ReferencedTable    ReferencedColumnName

脚本:

SELECT fk.Name AS 'FKName'
          ,OBJECT_NAME(fk.parent_object_id) 'ParentTable'
          ,cpa.name 'ParentColumnName'
          ,OBJECT_NAME(fk.referenced_object_id) 'ReferencedTable'
          ,cref.name 'ReferencedColumnName'
    FROM   sys.foreign_keys fk
           INNER JOIN sys.foreign_key_columns fkc
                ON  fkc.constraint_object_id = fk.object_id
           INNER JOIN sys.columns cpa
                ON  fkc.parent_object_id = cpa.object_id
                AND fkc.parent_column_id = cpa.column_id
           INNER JOIN sys.columns cref
                ON  fkc.referenced_object_id = cref.object_id
                AND fkc.referenced_column_id = cref.column_id