High angle view of Beijing Guomao.
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/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/VIEW_TABLES.htm
Have fun!
About the Author
Phil Molea
Sr. Information Developer, Vertica
Phil developed technical documentation in the areas of security and diagnostics for the Vertica Analytics Platform that enabled companies to extract value from their data at the speed and scale they need to thrive in today’s economy.
(Sadly, Phil passed away recently. He will be missed.)
