如何查找基于Enlisted表且带Status列WHERE子句的视图？

根据你的需求，以下是针对主流数据库的查询方案，可精准筛选出目标视图，减少手动排查量：

SQL Server #

1. 精准筛选：引用Enlisted表且WHERE子句包含Status列的视图 #

SELECT 
    v.name AS 视图名称,
    m.definition AS 视图定义
FROM 
    sys.views v
JOIN 
    sys.sql_modules m ON v.object_id = m.object_id
JOIN 
    sys.dm_sql_referenced_entities(v.name, 'OBJECT') r ON v.object_id = r.referencing_id
WHERE 
    r.referenced_entity_name = 'Enlisted'
    AND (m.definition LIKE '%WHERE%Status =%' 
         OR m.definition LIKE '%WHERE%Status IN%' 
         OR m.definition LIKE '%WHERE%Status >%' 
         OR m.definition LIKE '%WHERE%Status <%') -- 可根据实际条件扩展
ORDER BY 
    v.name;

2. 退而求其次：引用Enlisted表且带WHERE子句的视图 #

SELECT 
    v.name AS 视图名称,
    m.definition AS 视图定义
FROM 
    sys.views v
JOIN 
    sys.sql_modules m ON v.object_id = m.object_id
JOIN 
    sys.dm_sql_referenced_entities(v.name, 'OBJECT') r ON v.object_id = r.referencing_id
WHERE 
    r.referenced_entity_name = 'Enlisted'
    AND m.definition LIKE '%WHERE%'
ORDER BY 
    v.name;

MySQL #

1. 精准筛选目标视图 #

SELECT 
    TABLE_NAME AS 视图名称,
    VIEW_DEFINITION AS 视图定义
FROM 
    INFORMATION_SCHEMA.VIEWS
WHERE 
    TABLE_SCHEMA = '你的数据库名' -- 替换为实际数据库名称
    AND VIEW_DEFINITION LIKE '%Enlisted%'
    AND (VIEW_DEFINITION LIKE '%WHERE%Status =%' 
         OR VIEW_DEFINITION LIKE '%WHERE%Status IN%');

2. 退而求其次的筛选 #

SELECT 
    TABLE_NAME AS 视图名称,
    VIEW_DEFINITION AS 视图定义
FROM 
    INFORMATION_SCHEMA.VIEWS
WHERE 
    TABLE_SCHEMA = '你的数据库名'
    AND VIEW_DEFINITION LIKE '%Enlisted%'
    AND VIEW_DEFINITION LIKE '%WHERE%';

PostgreSQL #

1. 精准筛选目标视图 #

SELECT 
    table_name AS 视图名称,
    view_definition AS 视图定义
FROM 
    INFORMATION_SCHEMA.VIEWS
WHERE 
    table_schema = 'public' -- 替换为实际模式名称
    AND view_definition LIKE '%Enlisted%'
    AND (view_definition ~* 'WHERE\s+Status\s*=' 
         OR view_definition ~* 'WHERE\s+Status\s+IN');

2. 退而求其次的筛选 #

SELECT 
    table_name AS 视图名称,
    view_definition AS 视图定义
FROM 
    INFORMATION_SCHEMA.VIEWS
WHERE 
    table_schema = 'public'
    AND view_definition LIKE '%Enlisted%'
    AND view_definition ~* 'WHERE';

注意事项 #

• 若视图定义中存在注释包含Status或WHERE的情况，可通过正则表达式进一步精准匹配（如匹配WHERE后紧跟的有效条件），避免误命中注释内容。
• 对于嵌套视图，部分数据库的系统视图仅能识别直接引用关系，若需覆盖间接引用，可结合递归查询或数据库自带的依赖分析工具。

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