Display Tables Referenced by a View

Jim_Knicely

Vertica 9.2 introduces the new VIEW_TABLES system table that shows details about view-related dependencies, including the table that reference a view, its schema, and owner.

Example:

dbadmin=> CREATE TABLE base_table (c INT);
CREATE TABLE

dbadmin=> CREATE TABLE base_table2 (c INT);
CREATE TABLE

dbadmin=> CREATE OR REPLACE VIEW base_table_vw AS
dbadmin->   SELECT a.*
dbadmin->     FROM base_table a
dbadmin->     JOIN base_table2 b
dbadmin->    USING (c);
CREATE VIEW

dbadmin=> SELECT *
dbadmin->   FROM view_tables
dbadmin->  WHERE table_name = 'base_table_vw';
-[ RECORD 1 ]------------+------------------
table_id                 | 45035996280045078
table_schema             | public
table_name               | base_table_vw
reference_table_id       | 45035996280045062
reference_table_schema   | public
reference_table_name     | base_table
reference_table_owner_id | 45035996273704962
-[ RECORD 2 ]------------+------------------
table_id                 | 45035996280045078
table_schema             | public
table_name               | base_table_vw
reference_table_id       | 45035996280045074
reference_table_schema   | public
reference_table_name     | base_table2
reference_table_owner_id | 45035996273704962

Helpful Link:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/VIEW_TABLES.htm

Have fun!