How to find location of all tables under a schema in databricks

Question

Hi I'm using Azure databricks 11.3 LTS(include Apache Spark 3.3.0,scala 2.12) .I have a schema mydata under which I have around 25 tables.I need to find location of all tables under this schema. My sample output will look like in this format

| Table Name | Location |
|____________|__________|
|.....       |....      |

Tried sql command

Describe detail mydata.table_name

But this is a time consuming process as I need to write the sql code for 25 tables Can you please guide me how to do that?

Answer 1

I have tried the approach below:

table2_desc = spark.sql("DESCRIBE EXTENDED default.table2")
table1_desc = spark.sql("DESCRIBE EXTENDED default.table1")
print("Extended Description of default.table2:")
table2_desc.show(truncate=False)
print("Extended Description of default.table1:")
table1_desc.show(truncate=False)

Results:

Extended Description of default.table2:
+----------------------------+---------------------------------------------------+-------+
|col_name                    |data_type                                          |comment|
+----------------------------+---------------------------------------------------+-------+
|id                          |int                                                |NULL   |
|data                        |string                                             |NULL   |
|                            |                                                   |       |
|# Delta Statistics Columns  |                                                   |       |
|Column Names                |id, data                                           |       |
|Column Selection Method     |first-32                                           |       |
|                            |                                                   |       |
|# Detailed Table Information|                                                   |       |
|Catalog                     |spark_catalog                                      |       |
|Database                    |default                                            |       |
|Table                       |table2                                             |       |
|Created Time                |Fri Mar 29 05:22:19 UTC 2024                       |       |
|Last Access                 |UNKNOWN                                            |       |
|Created By                  |Spark 3.4.1                                        |       |
|Type                        |MANAGED                                            |       |
|Location                    |dbfs:/user/hive/warehouse/table2                   |       |
|Provider                    |delta                                              |       |
|Owner                       |root                                               |       |
|Is_managed_location         |true                                               |       |
|Table Properties            |[delta.minReaderVersion=1,delta.minWriterVersion=2]|       |
+----------------------------+---------------------------------------------------+-------+

Extended Description of default.table1:
+----------------------------+---------------------------------------------------+-------+
|col_name                    |data_type                                          |comment|
+----------------------------+---------------------------------------------------+-------+
|id                          |int                                                |NULL   |
|data                        |string                                             |NULL   |
|                            |                                                   |       |
|# Delta Statistics Columns  |                                                   |       |
|Column Names                |id, data                                           |       |
|Column Selection Method     |first-32                                           |       |
|                            |                                                   |       |
|# Detailed Table Information|                                                   |       |
|Catalog                     |spark_catalog                                      |       |
|Database                    |default                                            |       |
|Table                       |table1                                             |       |
|Created Time                |Fri Mar 29 05:20:22 UTC 2024                       |       |
|Last Access                 |UNKNOWN                                            |       |
|Created By                  |Spark 3.4.1                                        |       |
|Type                        |MANAGED                                            |       |
|Location                    |dbfs:/user/hive/warehouse/table1                   |       |
|Provider                    |delta                                              |       |
|Owner                       |root                                               |       |
|Is_managed_location         |true                                               |       |
|Table Properties            |[delta.minReaderVersion=1,delta.minWriterVersion=2]|       |
+----------------------------+---------------------------------------------------+-------+

In the code above, I am getting the extended descriptions of default.table2 and default.table1.