How to see all the databases and Tables in Databricks

user_14322212 0
Viewed 4734

i want to list all the tables in every database in Azure Databricks.

so i want the output to look somewhat like this:

Database | Table_name
Database1 | Table_1
Database1 | Table_2
Database1 | Table_3
Database2 | Table_1
etc..

This is what i have at the moment:

from pyspark.sql.types import *

DatabaseDF = spark.sql(f"show databases")
df = spark.sql(f"show Tables FROM {DatabaseDF}")
#df = df.select("databaseName")
#list = [x["databaseName"] for x in df.collect()]

print(DatabaseDF)
display(DatabaseDF)

df = spark.sql(f"show Tables FROM {schemaName}")
df = df.select("TableName")
list = [x["TableName"] for x in df.collect()]

## Iterate through list of schema
for x in list:
###  INPUT Required: Change for target table
    tempTable = x
    df2 = spark.sql(f"SELECT COUNT(*) FROM {schemaName}.{tempTable}").collect()
    for x in df2:
      rowCount = x[0]
      
    if rowCount == 0:
      print(schemaName + "." + tempTable + " has 0 rows")

but i'm not quite getting the results.

pyspark azure-databricks databricks azure python
1 Answers

There is a catalog property to spark session, probably what you are looking for :

spark.catalog.listDatabases()
spark.catalog.listTables("database_name")

listDatabases returns the list of database you have.
listTables returns for a certain database name, the list of tables.

You can do something like this for example :

[
    (table.database, table.name)
    for database in spark.catalog.listDatabases()
    for table in spark.catalog.listTables(database.name)
]

to get the list of database and tables.

EDIT: (thx @Alex Ott) even if this solution works fine, it is quite slow. Using directly some sql commands like show databases or show tables in ... should do the work faster.

user_50137520
Related