How to Show Tables From All Schemas with Presto
In Presto it is straightforward to show all tables in a schema e.g. If we have a MySQL data source/catalog that has a “demo” schema we use show tables in mysql.demo; but this only reveals the tables managed by that data source.
There is no equivalent way to show all tables in all schemas for a data source. However there’s the metastore to fall back on which we can query: In MySQL, Glue, Hive and others there is a schema called “information_schema” which contains a table called “tables”. This maintains a list of schemas and tables relating to that data source.
For a MySQL data source, here’s what Presto shows in a Presto table:
presto> select table_schema, table_name from mysql.information_schema.tables order by 1,2;
table_schema | table_name
--------------------+-----------------------------------------------
demo | geography
demo | state
demo | test
information_schema | applicable_roles
information_schema | columns
information_schema | enabled_roles
information_schema | roles
information_schema | schemata
information_schema | table_privileges
information_schema | tables
information_schema | views
sys | host_summary
sys | host_summary_by_file_io
sys | host_summary_by_file_io_type
...For Ahana’s integrated Hive metastore:
presto:demo> select table_schema, table_name from ahana_hive.information_schema.tables order by 1,2;
table_schema | table_name
--------------------+---------------------------
csv_test | yellow_taxi_trips
csv_test | yellow_taxi_trips_orc
csv_test | yellow_taxi_trips_staging
information_schema | applicable_roles
information_schema | columns
... This should help you show tables from all schemas.