Get metadata information about Delta Table using only SQL Editor (Query)?

[heron]

I'm trying to obtain the basic information and the storage location of the delta table, but without success.
Is there a way to get: storage location, type, catalog, schema, table name using SQL Editor through a query?
I can get the basic information (catalog, schema, table name) through the information_schema.tables table, but I couldn't find the rest (storage location, type).
I did a search and the AI-ChatGPT ​​suggested the query below but it's not working.

SELECT
t.table_catalog, t.table_schema, t.table_name, t.table_type,
detail.sizeInBytes, detail.location, detail.format
FROM
information_schema.tables t,
LATERAL (DESCRIBE DETAIL delta.`${t.table_catalog}.${t.table_schema}.${t.table_name}`) detail
WHERE
t.table_type = 'EXTERNAL' AND detail.format = 'delta'

Thank you very much if anyone can help

[brockb]

Hi Heron,

The output of the `DESCRIBE DETAIL table_name` includes the table format, size in bytes, location, and other information that sounds like what you're looking for. The only one I'm unsure about is the 'table type'. Here's the schema of the output of that command:

https://docs.databricks.com/en/delta/table-details.html

Hope it helps.

[jacovangelder]

I believe what you're looking for is DESCRIBE EXTENDED <table_name>.
This returns both delta storage location aswell as detailed table information such as type, table properties, catalog, schema, etc.