如何在SQL Server中列出所有视图（View）的连接（Join）信息

SQL Server没有内置系统视图直接存储视图的Join关联关系，需要通过解析视图的定义文本结合系统元数据来提取所需字段。以下是一个能覆盖常规场景的SQL脚本：

WITH ViewDefinitions AS (
    SELECT 
        SCHEMA_NAME(v.schema_id) AS schema_name,
        v.name AS view_name,
        sm.definition AS view_definition
    FROM sys.views v
    JOIN sys.sql_modules sm ON v.object_id = sm.object_id
    WHERE sm.definition LIKE '%JOIN%' -- 仅筛选包含Join逻辑的视图
),
JoinBlocks AS (
    SELECT 
        schema_name,
        view_name,
        TRIM(value) AS join_block,
        ROW_NUMBER() OVER (PARTITION BY schema_name, view_name ORDER BY CHARINDEX(value, view_definition)) AS join_sequence
    FROM ViewDefinitions
    -- 拆分出每个独立的Join片段（兼容INNER/LEFT JOIN）
    CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(view_definition, 'INNER JOIN', '|JOIN'), 'LEFT JOIN', '|JOIN'), '|')
    WHERE value LIKE '%JOIN%'
),
JoinConditions AS (
    SELECT 
        schema_name,
        view_name,
        join_sequence,
        TRIM(value) AS condition,
        ROW_NUMBER() OVER (PARTITION BY schema_name, view_name, join_sequence ORDER BY CHARINDEX(value, join_block)) AS column_sequence
    FROM JoinBlocks
    -- 拆分ON子句中的每个关联条件
    CROSS APPLY STRING_SPLIT(REPLACE(join_block, 'ON', '|'), '|')
    WHERE value LIKE '%=%'
)
SELECT 
    schema_name AS [schema],
    view_name AS [view],
    join_sequence,
    column_sequence,
    -- 提取左表关联列
    TRIM(SUBSTRING(condition, 1, CHARINDEX('=', condition) - 1)) AS table1_col_name,
    -- 提取左表名/别名
    CASE 
        WHEN CHARINDEX('.', SUBSTRING(condition, 1, CHARINDEX('=', condition) - 1)) > 0
        THEN TRIM(SUBSTRING(SUBSTRING(condition, 1, CHARINDEX('=', condition) - 1), 1, CHARINDEX('.', SUBSTRING(condition, 1, CHARINDEX('=', condition) - 1)) - 1))
        ELSE ''
    END AS table1_name,
    -- 提取右表关联列
    TRIM(SUBSTRING(condition, CHARINDEX('=', condition) + 1, LEN(condition))) AS table2_col_name,
    -- 提取右表名/别名
    CASE 
        WHEN CHARINDEX('.', SUBSTRING(condition, CHARINDEX('=', condition) + 1, LEN(condition))) > 0
        THEN TRIM(SUBSTRING(SUBSTRING(condition, CHARINDEX('=', condition) + 1, LEN(condition)), 1, CHARINDEX('.', SUBSTRING(condition, CHARINDEX('=', condition) + 1, LEN(condition))) - 1))
        ELSE ''
    END AS table2_name
FROM JoinConditions
ORDER BY schema_name, view_name, join_sequence, column_sequence;

注意事项 #

• 脚本仅支持常规的INNER JOIN/LEFT JOIN写法，以及ON子句中用=的单条件关联。如果涉及RIGHT JOIN/FULL JOIN、USING子句、多条件组合（AND/OR），需要调整字符串拆分逻辑。
• 若视图中表使用了别名，脚本会提取别名作为表名；如果未使用别名，table1_name/table2_name字段会为空，可结合sys.dm_sql_referenced_entities视图关联实际表名，需要额外扩展逻辑。
• 脚本仅解析当前视图的定义，不会递归处理嵌套视图中的Join关系。

内容的提问来源于stack exchange，提问作者Frank