查询视图以获取其列名 [英] Query a view to get its column names

问题描述

我有大量的 SQL 2008 R2 视图.我想知道视图中引用了哪些数据库字段.有没有办法可以查询架构以列出这些列名?

I have a large number of SQL 2008 R2 views. I would like to know which database fields are referenced in the views. Is there a way that I can query the schema to list out these column names?

推荐答案

针对 sys.sql_dependencies 使用此查询.

SELECT
    ViewName = O.name,
    ReferencedTableName = X.name,
    ReferencedColumnName = C.name,

    T.is_selected,
    T.is_updated,
    T.is_select_all,

    ColumnType = M.name,
    M.max_length,
    M.precision,
    M.scale
FROM
    sys.sql_dependencies AS T
    INNER JOIN sys.objects AS O ON T.object_id = O.object_id
    INNER JOIN sys.objects AS X ON T.referenced_major_id = X.object_id
    INNER JOIN sys.columns AS C ON 
        C.object_id = X.object_id AND
        C.column_id = T.referenced_minor_id
    INNER JOIN sys.types AS M ON 
        M.system_type_id = C.system_type_id AND
        M.user_type_id = C.user_type_id
WHERE
    O.type = 'V'
ORDER BY
    O.name,
    X.name,
    C.name

这篇关于查询视图以获取其列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！