sql-server - 在SQL Server View 中查找计算列

一位同事要求我帮助他们识别数据库中具有一个或多个计算列的视图。该数据库有数百个视图，因此他们试图找到一种自动的方式来完成此任务。我没有在预期的数据库中看到结果。这是一个例子：

--DROP TABLE dbo.Products

CREATE TABLE dbo.Products
(
    ProductID int IDENTITY (1,1) NOT NULL
  , QtyAvailable smallint
  , UnitPrice money
);

--DROP VIEW dbo.uvw_Products
CREATE VIEW dbo.uvw_Products
AS
    SELECT    ProductID
            , QtyAvailable
            , UnitPrice
            , (QtyAvailable * UnitPrice) AS InventoryValue
    FROM      dbo.Products;

-- Look at the view and find the computed column
SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],
        T.[name] AS [table_name], AC.[name] AS [column_name],
        TY.[name] AS system_data_type, AC.[max_length],
        AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded], AC.[is_computed]
FROM sys.[views] AS T
  INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
 INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]
WHERE T.[is_ms_shipped] = 0
AND T.[name] = 'uvw_Products'
ORDER BY T.[name], AC.[column_id]


-- Pulls up no results - no entries in sys.computed_columns
SELECT TOP 10 *
FROM sys.computed_columns C
INNER JOIN sys.views V ON C.[object_id] = V.[object_id]
WHERE V.[name] = 'uvw_Products'

从这个简单的示例可以看出，SQL Server似乎没有将值存储在is_computed列中。

我想念什么？我们如何在视图中找到计算列？

最佳答案

我知道这并不理想，我认为不存在针对此问题的万无一失的解决方案，但是根据您的命名约定，您可以对列名进行表sys视图的联接，以便查看存在和不存在的列在那里。

例如：

SELECT v.*
FROM
(
    SELECT  [Schema]        = OBJECT_SCHEMA_NAME(t.[object_id], DB_ID())
          , [table_name]    = t.[name]
          , [column_name]   = vc.[name]
    FROM sys.[views] t
    JOIN sys.[all_columns] vc ON t.[object_id] = vc.[object_id]
) v
LEFT JOIN
(
    SELECT  [Schema]        = OBJECT_SCHEMA_NAME(t.[object_id], DB_ID())
          , [table_name]    = t.[name]
          , [column_name]   = vc.[name]
    FROM sys.[tables] t
    JOIN sys.[all_columns] vc ON t.[object_id] = vc.[object_id]
) t
    ON t.[column_name] = v.[column_name]
WHERE t.[table_name] IS NULL

返回值：

Schema | table_name    | column_name
----------------------------------------
dbo    | uvw_Products  | InventoryValue

并且，如果您的视图包含名称如'uvw_Products'的源表，则您也可以在联接中使用该源表，以避免其他表中的列阻塞。

同样，它不是理想的，而是一种相对简单的解决方案，可以缩小搜索范围