DROP Multiple Tables in Database using Databricks

Question

I am trying to drop multiple tables in databrick scala using the following command

select 'DROP TABLE ' + tableName from ABC where tableName LIKE 'in%'

Error results saying

Error in SQL statement: AnalysisException: Table or view not found: ABC

However the ABC database is present

Thank you

Answer 1

No, it doesn't work this way... You need to use SHOW TABLES IN ... LIKE ... in combination with the explicit deletes. Something like this (in Python):

db_name = "ABC"
pattern = "in*"
tables = spark.sql(f"SHOW TABLES IN {db_name} LIKE '{pattern}'")
for row in tables.collect():
  spark.sql(f"DROP TABLE {db_name}.{row[1]}")

Answer 2

I found the following way in scala

val dbName="ABC"
val df = spark.sql(s"""show tables from """+ dbName)
df.createOrReplaceTempView("temp_tables")
val temp_tables = spark.sql("""select tableName from temp_tables where tableName like 'in%' """)

temp_tables.collect().foreach(row => println("DROP TABLE " + dbName + "."+ row.toString().replace("[", "").replace("]", "")))