Display Tables Referenced by a View
Jim_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!
0