Query to know all tables and columns name in delta lake

[sudhanshu1]

Hi all,

Does anyone know how to write simple SQL query to get all tables and columns name. In oracle we do ,select * from all tab columns. Similarly in SQL server we do select * from information schema . columns.

Do we have something like this in databricks?

[AmanSehgal]

To view columns in a table, use SHOW COLUMNS.

%sql
show columns in <schema_name>.<table_name>

To show all the tables in a column, use following PySpark code:

%python
 
schema_name = "default"
tbl_columns = {}
 
# Get all tables in a schema
tables = spark.sql("show tables from {}".format(schema_name)).\
               select('tableName').\
               rdd.map(lambda x : x[0]).\
               collect()
 
 # Get all columns in each table
for table in tables:
  tbl_columns[table]=spark.sql("show columns from {}.{}".format(schema_name,table)).\
              select('col_name').\
              rdd.map(lambda x : x[0]).\
              collect()
 
print(tbl_columns) #print JSON

[Kaniz · Accepted solution]

Hi @SUDHANSHU RAJ​ ,

Using Databricks, you do not get such a simplistic set of objects. What you have instead is:

• SHOW DATABASES command for viewing all databases/schemas
• SHOW TABLES command for viewing all tables within a database
• SHOW COLUMNS command for viewing all columns within a table — which, importantly, only includes the top-level name for nested columns

Source