How to find all tables with the same (similar) name in Databricks SQL

user_2670571 0
Viewed 280

Is there a sql query that can be used to find all of the tables in a Databricks instance for a given name? Something like

select * from information_schema.tables where lower(table_name) like 'org%';

If there isn't a way to do this in sql, is there another way to do this?

It looks like Databricks doesn't have something like the "information_schema" or other meta data tables that other database management systems have, are there plans to add something like this in future releases (thing like "show tables()" are really inadequate for so many use cases including the one discussed here).

databricks-sql databricks
2 Answers

You can use SHOW TABLES ... LIKE command for that. Please note that in contrast to "regular LIKE", instead of using % you need to use *, and that by default matching is case insensitive, so you don't need to do lower. Another thing to remember - you either need to specify database name explicitly, or lookup will be done only for current database:

SHOW TABLES LIKE 'org*'; -- current database
SHOW TABLES IN another_db LIKE 'org*'; -- another database
Alex_Ott_186270
user_52126140
Related