The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Display Tables Referenced by a View

Jim_KnicelyJim_Knicely - Select Field - Administrator

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!

Sign In or Register to comment.