List tables used by a view in Redshift

Query below lists views in a database with their definition.

Query

select u.view_schema as schema_name,
       u.view_name,
       u.table_schema as referenced_table_schema,
       u.table_name as referenced_table_name,
       v.view_definition
from information_schema.view_table_usage u
join information_schema.views v 
     on u.view_schema = v.table_schema
     and u.view_name = v.table_name
where u.table_schema not in ('information_schema', 'pg_catalog')
order by u.view_schema,
         u.view_name;

Columns

  • schema_name - view schema name
  • view_name - view name
  • referenced_table_schema - table schema name that view is refering to
  • referenced_table_name - table name that view is refering to
  • view_definition - view definition script

Rows

  • One row represents one view reference
  • Scope of rows: all views references in database
  • Ordered by view schema name, view name

Sample results

0
There are no comments. Click here to write the first comment.