CODEWITHSUNDEEP
apache-spark-sqldatabricksdatabricks-sql
John
John

Reputation: 3996

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

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).

Upvotes: 2

Views: 2094

Answers (2)

ASH
ASH

Reputation: 20342

SHOW TABLES [FROM | IN] db_name [LIKE 'pattern']

https://docs.databricks.com/spark/latest/spark-sql/language-manual/sql-ref-syntax-aux-show-tables.html

Upvotes: 1

Alex Ott
Alex Ott

Reputation: 87249

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

Upvotes: 4

Related Questions