Query to know all tables and columns name in delta lake

sudhanshu1 — Wed, 16 Feb 2022 22:33:02 GMT

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?

Re: Query to know all tables and columns name in delta lake

AmanSehgal — Wed, 16 Feb 2022 23:05:45 GMT

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

topic Re: Query to know all tables and columns name in delta lake in Data Engineering

Query to know all tables and columns name in delta lake

Re: Query to know all tables and columns name in delta lake