How to create a new table with the results of SHOW TABLES in Databricks SQL?

user_4949495 0
Viewed 155

I want to do aggregations on the result of

SHOW TABLES FROM databasename

Or create a new table with the result like

CREATE TABLE database.new_table AS (

     SHOW TABLES FROM database
);

But I'm getting multiple different errors if I try to do anything else with SHOW TABLES.

Is there another way of doing anything with the result of SHOW TABLES or another way creating a table with all the column names in a database? I have previously worked with Teradata where it's quite easy.

Edit: I only have access to Databricks SQL Analytics. So can only write in pure SQL.

databricks-sql-analytics databricks-sql
1 Answers

Another way of doing it:

spark.sql("use " + databasename)

df = spark.sql("show tables")

df.write.saveAsTable('databasename.new_table')

user_178497990
Related