How do I pull table name with column name list using databricks sql?

I am using Azure Databricks INFORMATION_Schema. Need list all tables and columns using SQL.

I used SHOW TABLES command for viewing all tables within a database. SHOW COLUMNS command for viewing all columns within a table. What am I missing? How do I get combine list of table name, and column name information using sql?

Ideally you are using Unity Catalog, in which case Databricks has you covered with a built-in system table containing all table and column information in the system.information_schema schema.

The system.information_schema.columns table has information about the columns, but also has the table_name column that relates it to the table the column is part of.

https://docs.databricks.com/sql/language-manual/sql-ref-information-schema.html

TL;DR With Unity Catalog:

SELECT 
  CONCAT(table_catalog, '.', table_schema, '.', table_name) AS `table`,
  column_name,
  data_type
FROM system.information_schema.columns
ORDER BY 1

Example python notebook (without Unity Catalog)

database_list = ["database_1", "database_2"]

for database in database_list:
  for row in spark.sql(f"show tables from {database}").collect():
    full_table_name = row["database"] + '.' + row["tableName"]
    print(full_table_name)
    spark.sql(f"describe {full_table_name}").show()