技术需求：查找包含未在对应Table中存在列的View对象

Got it, let's figure out how to track down those problematic views that reference columns no longer present in their underlying tables. This exact scenario happens all the time when someone modifies a table (like dropping a column) but forgets to update dependent views—just like your example where Table1 lost column2 but View1 still tries to reference it.

First, let's recap your example for clarity:

Table1 initially has columns column1 and column2. View1 is created to select both columns from Table1. Later, column2 is dropped from Table1, but View1's definition isn't updated. Now View1 still includes column2, which no longer exists in Table1. We need to find all such views.

Below are solutions for the most common database systems:

1. SQL Server #

We'll use SQL Server's system catalog views to map views to their base tables and compare column existence:

SELECT 
    v.name AS ViewName,
    c.name AS MissingColumn,
    t.name AS BaseTable
FROM 
    sys.views v
JOIN 
    sys.sql_expression_dependencies sed 
        ON v.object_id = sed.referencing_id
        AND sed.referenced_entity_name IS NOT NULL -- Ensure we're referencing a table
JOIN 
    sys.tables t 
        ON OBJECT_ID(sed.referenced_entity_name) = t.object_id
JOIN 
    sys.columns c 
        ON v.object_id = c.object_id
LEFT JOIN 
    sys.columns tc 
        ON t.object_id = tc.object_id 
        AND c.name = tc.name
WHERE 
    tc.object_id IS NULL -- Column exists in view but not in base table
ORDER BY 
    v.name, c.name;

How this works: #

• sys.views lists all views in the database
• sys.sql_expression_dependencies links views to the tables they reference
• We join view columns (sys.columns for views) to base table columns (sys.columns for tables)
• The LEFT JOIN + WHERE tc.object_id IS NULL filters for columns that exist in the view but not the base table

2. PostgreSQL #

PostgreSQL's information_schema makes this straightforward with standardized views:

SELECT 
    v.table_name AS ViewName,
    c.column_name AS MissingColumn,
    vt.table_name AS BaseTable
FROM 
    information_schema.views v
JOIN 
    information_schema.columns c 
        ON v.table_name = c.table_name 
        AND v.table_schema = c.table_schema
JOIN 
    information_schema.view_table_usage vt 
        ON v.table_name = vt.view_name 
        AND v.table_schema = vt.view_schema
LEFT JOIN 
    information_schema.columns tc 
        ON vt.table_name = tc.table_name 
        AND vt.table_schema = tc.table_schema 
        AND c.column_name = tc.column_name
WHERE 
    tc.column_name IS NULL -- Column not found in base table
    AND v.table_schema = 'public' -- Replace with your schema if needed
ORDER BY 
    v.table_name, c.column_name;

Notes for PostgreSQL: #

• Adjust v.table_schema to match your target schema (default is public)
• This handles views that reference multiple tables, but you may see duplicate rows if a view joins multiple tables—you can add filters if you need to target specific tables

3. MySQL #

MySQL's information_schema has the data we need, though we need to join across a few views to map dependencies:

SELECT 
    c.TABLE_NAME AS ViewName,
    c.COLUMN_NAME AS MissingColumn,
    kcu.REFERENCED_TABLE_NAME AS BaseTable
FROM 
    INFORMATION_SCHEMA.COLUMNS c
JOIN 
    INFORMATION_SCHEMA.VIEWS v 
        ON c.TABLE_NAME = v.TABLE_NAME 
        AND c.TABLE_SCHEMA = v.TABLE_SCHEMA
JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
        ON v.TABLE_NAME = kcu.TABLE_NAME 
        AND v.TABLE_SCHEMA = kcu.TABLE_SCHEMA
LEFT JOIN 
    INFORMATION_SCHEMA.COLUMNS tc 
        ON kcu.REFERENCED_TABLE_NAME = tc.TABLE_NAME 
        AND kcu.REFERENCED_TABLE_SCHEMA = tc.TABLE_SCHEMA 
        AND c.COLUMN_NAME = tc.COLUMN_NAME
WHERE 
    tc.COLUMN_NAME IS NULL
    AND c.TABLE_SCHEMA = 'your_database' -- Replace with your database name
ORDER BY 
    c.TABLE_NAME, c.COLUMN_NAME;

Tips for MySQL: #

• For views with complex definitions (like computed columns or joins), this query might flag legitimate computed columns as "missing"—you'll want to manually review those cases to avoid false positives

General Notes #

• Permissions: Make sure you have the necessary permissions to query system views (e.g., VIEW SERVER STATE in SQL Server, SELECT on information_schema in PostgreSQL/MySQL)
• Complex Views: For views that use computed columns, subqueries, or functions, these queries may return false positives. Always manually verify results before modifying views.
• Fixes: Once you identify problematic views, update their definitions to remove references to missing columns, or add the columns back to the base table if that's intended.

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