SQL查询视图定义：处理函数与运算符以提取源表及列

我需要解析SQL视图定义以提取所使用的源表和列，目前通过SELECT * FROM INFORMATION_SCHEMA.VIEWS获取视图定义，已有查询可将普通源表和列拆分为单独行和列，但在处理COALESCE等函数及+、-、*、/运算符时遇到问题——无法将包含多个源列的视图列拆分为单独的行和字段。

示例场景 #

测试代码 #

SELECT 'COALESCE(HEADER.ID1,HEADER.ID2,0) AS ID' AS q INTO #TEMP

SELECT q,
SUBSTRING(q,CHARINDEX( 'COALESCE(',q,0)+9,CHARINDEX( '.',q,0)-10) AS [First Table],
SUBSTRING(q,CHARINDEX( 'COALESCE(',q,0)+9,CHARINDEX( ',',q,0)-10) AS [First Table And Column],
SUBSTRING(q,CHARINDEX( ',',q,0)+1,(CHARINDEX( ',0)',q,0)-CHARINDEX( ',',q,0))-1)  AS [Second Table And Column],
FROM #TEMP

--DROP TABLE #TEMP

当前结果 #

期望结果 #

现有完整查询代码 #

WITH tmp (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ColumnRow, ViewDefinition) AS
(
   SELECT
       TABLE_CATALOG,
       TABLE_SCHEMA,
       TABLE_NAME,
       LEFT(ViewDefinition, CHARINDEX(CHAR(13)+CHAR(10), ViewDefinition + CHAR(13)+CHAR(10)) + 1),
       STUFF(ViewDefinition, 1, CHARINDEX(CHAR(13)+CHAR(10), ViewDefinition + CHAR(13)+CHAR(10)), '')
   FROM
        (SELECT
           TABLE_CATALOG,
           TABLE_SCHEMA,
           TABLE_NAME,
        SUBSTRING(VIEW_DEFINITION,CHARINDEX('SELECT',VIEW_DEFINITION)+6,CHARINDEX('FROM',VIEW_DEFINITION)-CHARINDEX('SELECT',VIEW_DEFINITION)-18) AS ViewDefinition
        FROM INFORMATION_SCHEMA.VIEWS
        ) ViewData
   UNION all
   SELECT
       TABLE_CATALOG,
       TABLE_SCHEMA,
       TABLE_NAME,
       LEFT(ViewDefinition, CHARINDEX(CHAR(13)+CHAR(10), ViewDefinition + CHAR(13)+CHAR(10)) + 1),
       STUFF(ViewDefinition, 1, CHARINDEX(CHAR(13)+CHAR(10), ViewDefinition + CHAR(13)+CHAR(10)), '')
   FROM tmp
   WHERE
       ViewDefinition > ''
)

SELECT
  TABLE_CATALOG,
  TABLE_SCHEMA,
  TABLE_NAME,
  RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(ColumnRow,
        CASE WHEN CHARINDEX('.',ColumnRow) = 0 THEN CHARINDEX('.',ColumnRow)  
            WHEN 
                CHARINDEX('CAST(',ColumnRow) > 0 
                OR CHARINDEX('CASE WHEN',ColumnRow) > 0  
                OR CHARINDEX('COALESCE',ColumnRow) > 0 
                OR CHARINDEX('GETDATE',ColumnRow) > 0 
                OR CHARINDEX('SUM(',ColumnRow) > 0 
                OR CHARINDEX('+',ColumnRow) > 0 
                OR CHARINDEX('-',ColumnRow) > 0 
                OR CHARINDEX('*',ColumnRow) > 0 
                OR CHARINDEX('/',ColumnRow) > 0    
            THEN 
                '' 
            ELSE 
                REPLACE(CHARINDEX('.',ColumnRow)-1,CHAR(9),'')
        END
        ),CHAR(13)+CHAR(10), ''),' ', ''),'ISNULL(',''),CHAR(9),''),CHAR(32),''),CHAR(10),''),CHAR(13),''),CHAR(160),''))) AS LeanTableName,
  CASE 
    WHEN 
      CHARINDEX('CAST(',ColumnRow) > 0
      OR CHARINDEX('CASE WHEN',ColumnRow) > 0  
      OR CHARINDEX('COALESCE',ColumnRow) > 0 
      OR CHARINDEX('GETDATE',ColumnRow) > 0 
      OR CHARINDEX('SUM(',ColumnRow) > 0 
      OR CHARINDEX('+',ColumnRow) > 0 
      OR CHARINDEX('-',ColumnRow) > 0 
      OR CHARINDEX('*',ColumnRow) > 0 
      OR CHARINDEX('/',ColumnRow) > 0    
    THEN 
      ''    
    ELSE 
      REPLACE(REPLACE(REPLACE ( 
      LEFT(RIGHT(ColumnRow,LEN(ColumnRow)-CHARINDEX('.',ColumnRow)),
      CHARINDEX(' AS ',RIGHT(ColumnRow,LEN(ColumnRow)-CHARINDEX('.',ColumnRow)-1))), ',''0'')',''),','''')',''), ')', '')
  END AS LeanColumnName,
  CASE
    WHEN 
      CHARINDEX('CAST(',ColumnRow) > 0   
      or CHARINDEX('nvarchar',ColumnRow) > 0  
      or CHARINDEX('decimal',ColumnRow) > 0  
    THEN 
      LTRIM(REPLACE(SUBSTRING(ColumnRow,CHARINDEX(') AS ',ColumnRow)+4,100),',',''))
    ELSE 
      REPLACE(SUBSTRING(ColumnRow,CHARINDEX(' AS ',ColumnRow)+4,100),',','') 
  END AS ColumnName,  
  LTRIM(LEFT( LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(ColumnRow, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),CHAR(160), CHAR(32)),CHAR(9),CHAR(32)))), LEN( LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(ColumnRow, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),CHAR(160), CHAR(32)),CHAR(9),CHAR(32))))) - 1)) AS RowValue
FROM tmp
WHERE (ColumnRow LIKE '% AS %' and ColumnRow not like '%--%')
GO

解决方案 #

针对COALESCE的单场景处理 #

可以通过提取函数内部参数、拆分过滤后再拆分表和列的方式，得到期望的行格式：

SELECT 'COALESCE(HEADER.ID1,HEADER.ID2,0) AS ID' AS q INTO #TEMP;

WITH SplitCTE AS (
    -- 提取COALESCE括号内的所有参数
    SELECT 
        q,
        TRIM(value) AS param
    FROM #TEMP
    CROSS APPLY STRING_SPLIT(
        SUBSTRING(q, CHARINDEX('COALESCE(', q) + 9, CHARINDEX(')', q) - CHARINDEX('COALESCE(', q) - 9), 
        ','
    )
    -- 过滤掉常量值，仅保留表.列格式的参数
    WHERE TRIM(value) LIKE '%.%'
)
-- 拆分表名与列名
SELECT 
    q,
    LEFT(param, CHARINDEX('.', param) - 1) AS [Table],
    RIGHT(param, LEN(param) - CHARINDEX('.', param)) AS [Column]
FROM SplitCTE;

DROP TABLE #TEMP;

扩展到完整查询的改进思路 #

现有查询在遇到函数或运算符时直接返回空值，需要修改这部分逻辑：

1. 识别包含函数（如COALESCE、ISNULL）或运算符的ColumnRow
2. 提取这些表达式中的所有表.列格式引用：
   • 对于函数，提取括号内的参数列表并拆分
   • 对于运算符表达式，按运算符拆分后过滤出表列引用
3. 将每个提取出的表列引用转换为单独行，关联原视图的元数据信息

例如，可以在现有CTE后加入额外的递归或拆分逻辑，对特殊行进行处理，替代原查询中返回空值的分支。

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