查找列依赖

2022-01-16 00:00:00 tsql sql-server-2008 sql-server sql-server-2008-r2

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

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

例如:

表:SomeTable

Table: SomeTable

列:col1 PK,col2,col3

Cols: col1 pk, col2, col3

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

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

推荐答案

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

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 :)

祝你好运！


    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'

相关文章