Is there any way to display all columns and tables in a schema in Presto?

Question

I've been struggling a bit with finding, the tables and columns in a schema in Presto as below in classical PL/SQL: "select column_name, table_name from all_tab_columns?" In the presto doc didn't found anything pointing to this. You can show columns in a table and tables in a schema but not all tables with their respective columns in a schema. One use case would be for finding a table which contains a desired column name as: "select column_name, table_name from all_tab_columns where column_name like '%something_potentially_useful%'" A similar post is answering for how to see all tables and schemas in an information_schema but unfortunately information_schema does not contain column_name as descriptor.

Thank you upfront!

Answer 1

information_schema.columns contains all the available information:

SELECT * FROM <catalog>.information_schema.columns

As of Presto 327, The information_schema.columns table has the following columns:

presto:default> DESCRIBE tpch.information_schema.columns;
      Column      |  Type   | Extra | Comment
------------------+---------+-------+---------
 table_catalog    | varchar |       |
 table_schema     | varchar |       |
 table_name       | varchar |       |
 column_name      | varchar |       |
 ordinal_position | bigint  |       |
 column_default   | varchar |       |
 is_nullable      | varchar |       |
 data_type        | varchar |       |
 comment          | varchar |       |
 extra_info       | varchar |       |
(10 rows)