如何通过information_schema获取视图列与原表列的投影映射关系？

Great question! I’ve run into this exact scenario before—trying to trace back a view’s aliased column to its original source table column, and hitting the wall with INFORMATION_SCHEMA.VIEW_COLUMN_USAGE only telling you which columns are used, not how they’re renamed in the view.

The Short Answer #

Unfortunately, there is no standard INFORMATION_SCHEMA object that directly stores the mapping between a view's aliased columns and their original source columns. VIEW_COLUMN_USAGE only tracks dependencies (i.e., that the view uses persons.identifier), but it doesn’t capture the AS id alias mapping you’re looking for.

Workarounds by Database #

While there’s no universal INFORMATION_SCHEMA solution, most databases let you retrieve this detail by parsing the view’s raw definition, which is stored in the INFORMATION_SCHEMA. Here’s how to do it in common systems:

1. PostgreSQL

Use the pg_get_viewdef() function to fetch the view’s formatted definition, then extract the alias mappings from the query string:

SELECT pg_get_viewdef('your_view_name', true);

This returns the full view SQL, where you’ll see identifier AS id clearly. For programmatic use, you could leverage string functions or a SQL parser library to automate extracting these mappings.

2. MySQL

Query INFORMATION_SCHEMA.VIEWS to get the view’s creation statement, then inspect the AS clauses:

SELECT VIEW_DEFINITION 
FROM INFORMATION_SCHEMA.VIEWS 
WHERE TABLE_SCHEMA = 'your_database_name' 
  AND TABLE_NAME = 'your_view_name';

The VIEW_DEFINITION column contains the complete CREATE VIEW syntax, so you can parse it to map the view’s id column back to persons.identifier.

3. SQL Server

Pull the view definition from either INFORMATION_SCHEMA.VIEWS or the system catalog sys.sql_modules:

-- Using INFORMATION_SCHEMA
SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'your_view_name';

-- Using system catalogs (more reliable for complex views)
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('your_view_name');

Just like with other databases, you’ll need to parse the definition string to uncover the alias-to-source-column relationship.

Why No Direct INFORMATION_SCHEMA Object? #

The SQL standard’s INFORMATION_SCHEMA is built to capture high-level metadata about objects and their dependencies, not granular query syntax details like column aliases. This level of specific parsing is left to database-specific tools or functions that expose the raw view definition.

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