查找列依赖项 [英] Find Column dependency

问题描述

如何查找依赖于表格特定列的对象.

例如:

表:SomeTable

列:col1 pk,col2，col3

我想找到所有依赖于 col1 (Pk) 的对象

解决方案

试试这个查询，它会给你一些我认为你正在寻找的结果.
要过滤，请搜索 c1.name 或 c2.name 列中的值.
要查找对某个列的所有引用，请使用 c2.name 作为列名，使用 OBJECT_NAME(k.referenced_object_id) 作为包含 c2 列的表:)

祝你好运！

<预><代码>选择 OBJECT_NAME(k.parent_object_id) 作为 parentTable, c1.name 作为 parentColumn, OBJECT_NAME(k.referenced_object_id) 作为referencedTable, c2.name asreferencedColumn来自 sys.foreign_keys k内连接 sys.foreign_key_columns ff.parent_object_id = k.parent_object_id和 f.constraint_object_id = k.object_id内连接 sys.columns c1在 c1.column_id = f.parent_column_id和 c1.object_id = k.parent_object_id内连接 sys.columns c2在 c2.column_id = f.referenced_column_id和 c2.object_id = k.referenced_object_id其中 c2.name = '列'和 OBJECT_NAME(k.referenced_object_id) = '表'

How to find objects which depend on particular column of table.

Ex:

Table: SomeTable

Cols: col1 pk, col2, col3

I want to find all the objects which are dependent on col1 (Pk)

解决方案

Try this query, it will get you some results that i think you are looking for.
To filter, search for the value in the c1.name or c2.name column.
To look for all the references to a certain column, use the c2.name for the column name and the OBJECT_NAME(k.referenced_object_id) as the table which holds the c2 column :)

Good Luck!

    select  OBJECT_NAME(k.parent_object_id) as parentTable
          , c1.name as parentColumn
          , OBJECT_NAME(k.referenced_object_id) as referencedTable
          , c2.name as referencedColumn
    from    sys.foreign_keys k
            inner join sys.foreign_key_columns f
              on  f.parent_object_id = k.parent_object_id
              and f.constraint_object_id = k.object_id
            inner join sys.columns c1
              on  c1.column_id = f.parent_column_id
              and c1.object_id = k.parent_object_id
            inner join sys.columns c2
              on  c2.column_id = f.referenced_column_id
              and c2.object_id = k.referenced_object_id
    where   c2.name = 'Column'
    and     OBJECT_NAME(k.referenced_object_id) = 'Table'

这篇关于查找列依赖项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！