The following can be used to show table in the current schema or a specified schema respectively:
show tables;
show tables in my_schema;
This documented here: https://docs.databricks.com/spark/latest/spark-sql/language-manual/show-tables.html
Is there a way to show all table in all databases?
Are there metadata tables in Databricks/Spark (similar to the all_ or dba_ tables in Oracle or the information_schema in MySql)? Is there a way to do more specific queries about database objects in Databricks? Something like this:
select * from i_dont_know_what where lower(table_name) like '%gold%' and schema = 'myschema';
Can't you use spark catalog apis on databricks? please try this-
val tuples: Map[String, String] = spark.catalog.listDatabases().collect().flatMap(db =>
spark.catalog.listTables(db.name).collect().map(x => (db.name, x.name))
).toMap
This looks like its pulling all of the data and then filtering. I'm currently doing something in Java. I was hoping there is a way to do this in sql.
Here there is no expression to pull the data from the db. we are just pulling the metadata
@Som Could you please give the same code in Python?