INFORMATION_SCHEMA外键

Question

我有这样的代码，它允许我识别tsql过程中的标识列：

SELECT 
    c.column_name, (columnproperty(object_id(@table_name),c.column_name,'isIdentity')) pk
from information_schema.columns c
where c.table_name = @table_name

有人能帮我修改这段代码，允许识别外键吗？

提前感谢

Answer 1

可以使用此table语句获取引用特定表的所有外键：

;WITH ReferencingFK AS 
(
    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',
        fk.delete_referential_action,
        fk.update_referential_action
    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
)
SELECT 
    FKName,
    ParentTable,
    ParentColumnName,
    ReferencedTable,
    ReferencedColumnName
FROM 
    ReferencingFK
WHERE
    ReferencedTable = 'YourTableNameHere'
ORDER BY 
    ParentTable, ReferencedTable, FKName

如果您想要所有外键，那么只需完全删除WHERE子句即可。