如何在SQL Server视图中识别计算列？现有查询无预期结果

我刚踩过一模一样的坑！SQL Server在这里的设计很容易让人误解——视图里的计算列根本不会被标记在sys.all_columns.is_computed里，也不会出现在sys.computed_columns系统视图中。

为什么你的查询没结果？ #

sys.computed_columns和sys.all_columns.is_computed只针对表的持久化计算列（就是创建表时用AS定义、甚至可以持久化存储的那种计算列）。而视图里的计算列只是查询语句中的动态表达式，SQL Server不会把它们当成“正式”的计算列记录到这些系统视图里，自然查不到结果。

正确的识别方法 #

下面是三种可靠的方式，帮你批量识别所有视图中的计算列：

方法1：利用依赖关系视图sys.sql_expression_dependencies #

视图里的计算列必然依赖其他表/列，通过查询依赖元数据可以精准判断：

SELECT
    OBJECT_SCHEMA_NAME(v.object_id) AS [Schema],
    v.name AS ViewName,
    c.name AS ColumnName,
    CASE WHEN ed.referencing_minor_id IS NOT NULL THEN 'Yes' ELSE 'No' END AS IsComputedColumn,
    ed.referenced_entity_name AS SourceTable,
    ed.referenced_minor_name AS SourceColumn
FROM sys.views v
INNER JOIN sys.all_columns c 
    ON v.object_id = c.object_id
LEFT JOIN sys.sql_expression_dependencies ed 
    ON v.object_id = ed.referencing_id 
    AND c.column_id = ed.referencing_minor_id
WHERE v.is_ms_shipped = 0
-- 筛选特定视图可加此行
-- AND v.name = 'uvw_Products'
ORDER BY [Schema], ViewName, c.column_id

这个方法不需要解析SQL文本，准确性很高，适合批量处理所有视图。

方法2：解析视图的定义文本 #

如果需要直接看到计算表达式，可以用OBJECT_DEFINITION()提取视图的SQL代码：

SELECT
    OBJECT_SCHEMA_NAME(v.object_id) AS [Schema],
    v.name AS ViewName,
    c.name AS ColumnName,
    -- 提取计算表达式（简化版，复杂视图需调整字符串逻辑）
    SUBSTRING(
        OBJECT_DEFINITION(v.object_id),
        CHARINDEX(c.name + ' AS ', OBJECT_DEFINITION(v.object_id)) + LEN(c.name) + 4,
        CHARINDEX(',', OBJECT_DEFINITION(v.object_id), CHARINDEX(c.name + ' AS ', OBJECT_DEFINITION(v.object_id))) 
        - (CHARINDEX(c.name + ' AS ', OBJECT_DEFINITION(v.object_id)) + LEN(c.name) + 4)
    ) AS ComputedExpression
FROM sys.views v
INNER JOIN sys.all_columns c 
    ON v.object_id = c.object_id
WHERE v.is_ms_shipped = 0
    AND CHARINDEX(c.name + ' AS ', OBJECT_DEFINITION(v.object_id)) > 0
-- AND v.name = 'uvw_Products'
ORDER BY [Schema], ViewName, c.column_id

注意：复杂视图（含子查询、换行、注释）需要更精细的字符串处理或正则匹配。

方法3：用动态元数据函数sys.dm_exec_describe_first_result_set #

这个函数会模拟执行视图查询，返回结果集的准确元数据，其中is_computed字段会正确标记计算列：

-- 查询单个视图的例子
DECLARE @ViewFullName NVARCHAR(MAX) = QUOTENAME(OBJECT_SCHEMA_NAME(OBJECT_ID('uvw_Products'))) + '.' + QUOTENAME('uvw_Products')
SELECT
    name AS ColumnName,
    is_computed AS IsComputed
FROM sys.dm_exec_describe_first_result_set('SELECT * FROM ' + @ViewFullName, NULL, 0)

-- 批量处理所有视图的游标脚本
DECLARE @ViewName NVARCHAR(255), @SchemaName NVARCHAR(255), @SQL NVARCHAR(MAX)
DECLARE ViewCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME(object_id), name FROM sys.views WHERE is_ms_shipped = 0

OPEN ViewCursor
FETCH NEXT FROM ViewCursor INTO @SchemaName, @ViewName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'SELECT ''' + @SchemaName + ''' AS [Schema], ''' + @ViewName + ''' AS ViewName, name AS ColumnName, is_computed AS IsComputed FROM sys.dm_exec_describe_first_result_set(''SELECT * FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ViewName) + ''', NULL, 0)'
    EXEC sp_executesql @SQL
    FETCH NEXT FROM ViewCursor INTO @SchemaName, @ViewName
END

CLOSE ViewCursor
DEALLOCATE ViewCursor

这个方法准确性最高，但批量处理大量视图时会有一定性能开销。

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