DESCRIBE TABLE
Applies to: 
Databricks SQL Databricks Runtime
Returns the basic metadata information of a table. The metadata information includes column name, column type and column comment. Optionally, you can specify a partition spec or column name to return the metadata pertaining to a partition or column respectively. With Delta tables, not all fields are returned.
The metadata is either returned in report form or as a JSON document.
The non-JSON output of DESCRIBE TABLE can change, and new columns might be appended over time. Avoid relying on the output meeting a specific size or schema. For programmatic consumption, use DESCRIBE TABLE ... AS JSON.
Syntax
{ DESC | DESCRIBE } [ TABLE ] [ EXTENDED ] table_name { [ PARTITION clause ] | [ column_name ] } [ AS JSON ]
For compatibility FORMATTED can be specified as a synonym for EXTENDED.
Parameters
-
EXTENDEDIf specified display detailed information about the specified columns, including the column statistics collected by the command, and additional metadata information (such as schema qualifier, owner, and access time).
In Databricks SQL and Databricks Runtime 19 and above, the extended metadata for a view includes
SQL Path, the PATH captured when the view was created. WithAS JSON, that value is thesql_pathfield. -
Identifies the table to be described. The name may not use a temporal specification or options specification. If the table cannot be found Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.
-
An optional parameter directing Databricks SQL to return addition metadata for the named partitions.
-
An optional parameter with the column name that needs to be described. Currently nested columns are not allowed to be specified.
JSON format is supported for individual columns.
-
AS JSONApplies to:
Databricks SQL Databricks Runtime 16.2 and aboveOptionally returns the table metadata as a JSON string instead of a human-readable report. Use this format when parsing the result using a program.
Only supported when
EXTENDEDformat is specified.
Parameters partition_spec and column_name are mutually exclusive and cannot be specified together.
For a parameterized metric view, DESCRIBE TABLE lists the view parameters after the column metadata under a # Parameters heading, reusing the col_name, data_type, and comment fields. Parameter default values are not shown. With AS JSON, parameters are returned in a parameters field that includes each parameter's name, type, and default. Parameters require Databricks Runtime 18.2 and above.
For a Unity Catalog metric view that has relationships (Beta), DESCRIBE TABLE EXTENDED lists them under a # Constraints heading, with one row for each relationship. With AS JSON, relationships are returned in the table_constraints field. Relationships require Databricks Runtime 19 and above.
JSON formatted output
When AS JSON is specified, the output is returned as a JSON string. The following schema is supported:
{
"table_name": "<table_name>",
"catalog_name": "<catalog_name>",
"schema_name": "<schema_name>",
"namespace": ["<schema_name>"],
"type": "<table_type>",
"provider": "<provider>",
"columns": [
{
"name": "<name>",
"type": <type_json>,
"comment": "<comment>",
"nullable": <boolean>,
"default": "<default_val>",
"is_measure": <boolean>, // Included only for measure columns of metric views
}
],
"parameters": [ // Included only for parameterized metric views; requires Databricks Runtime 18.2 and above
{
"name": "<name>",
"type": <type_json>,
"default": "<default_val>"
}
],
"partition_values": {
"<col_name>": "<val>"
},
"partition_columns": ["col1", "col2"],
"location": "<path>",
"view_text": "<view_text>",
"view_original_text": "<view_original_text>",
"view_schema_mode": "<view_schema_mode>",
"view_catalog_and_namespace": "<view_catalog_and_namespace>",
"view_query_output_columns": ["<col_name>"],
"sql_path": [ // Available in Databricks Runtime 19 and above, included only for views, one object per path element
{ "catalog_name": "<catalog_name>", "namespace": ["<schema_name>"] }
],
"comment": "<comment>",
"table_properties": {
"property1": "<property1>",
"property2": "<property2>"
},
"statistics": {
"num_rows": <count>,
"size_in_bytes": <bytes>,
"table_change_stats": {
"inserted": <count>,
"deleted": <count>,
"updated": <count>,
"change_percent": <percent_changed_float>
}
},
"storage_properties": {
"property1": "<property1>",
"property2": "<property2>"
},
"serde_library": "<serde_library>",
"input_format": "<input_format>",
"output_format": "<output_format>",
"num_buckets": <num_buckets>,
"bucket_columns": ["<col_name>"],
"sort_columns": ["<col_name>"],
"created_time": "<timestamp_ISO-8601>",
"created_by": "<created_by>",
"last_access": "<timestamp_ISO-8601>",
"partition_provider": "<partition_provider>",
"collation": "<default_collation>", // Available in Databricks Runtime 16.4 and above
"language": "<language>", // Language such as YAML, included only for metric views
"row_filter": { // Available in Databricks Runtime 17.0 and above
"filter_function": {
"catalog_name": "<catalog_name>",
"schema_name": "<schema_name>",
"function_name: "<function_name>",
"specific_name": "<specific_name>"
},
"arguments": ["<arg1>", "<arg2>"]
},
"column_masks": [ // Available in Databricks Runtime 17.0 and above
{"column_name": "<column_name>",
"mask_function": {
"catalog_name": "<catalog_name>",
"schema_name": "<schema_name>",
"function_name: "<function_name>",
"specific_name": "<specific_name>"
},
"arguments": ["<arg1>", "<arg2>"]
}
]
"refresh_information": { // Available in Databricks Runtime 17.3 and above, included only for materialized views and streaming tables
"last_refreshed_at": "<timestamp_ISO-8601>",
"last_refresh_type": "<refresh_type>",
"latest_refresh_status": "<refresh_status>",
"latest_refresh_link": "<refresh_url>",
"refresh_schedule": "<schedule>"
},
"predictive_optimization_evaluations": { // Available in Databricks Runtime 18 and above, included only for managed tables with predictive optimization enabled
"<operation_type>": {
"last_run_status": "<last_run_status>",
"last_run_time": "<timestamp_ISO-8601>",
"latest_evaluation_time": "<timestamp_ISO-8601>",
"latest_evaluation": "<latest_evaluation>"
}
}
}
Below are the schema definitions for <type_json>:
SQL Type | JSON Representation |
|---|---|
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
|
Examples
-- Creates a table `customer`. Assumes current schema is `salesdb`.
> CREATE TABLE customer(
cust_id INT,
state VARCHAR(20),
name STRING COMMENT 'Short name'
)
USING parquet
PARTITIONED BY (state);
> INSERT INTO customer PARTITION (state = 'AR') VALUES (100, 'Mike');
-- Returns basic metadata information for unqualified table `customer`
> DESCRIBE TABLE customer;
col_name data_type comment
----------------------- --------- ----------
cust_id int null
name string Short name
state string null
# Partition Information
# col_name data_type comment
state string null
-- Returns basic metadata information for qualified table `customer`
> DESCRIBE TABLE salesdb.customer;
col_name data_type comment
----------------------- --------- ----------
cust_id int null
name string Short name
state string null
# Partition Information
# col_name data_type comment
state string null
-- Returns additional metadata such as parent schema, owner, access time etc.
> DESCRIBE TABLE EXTENDED customer;
col_name data_type comment
---------------------------- ------------------------------ ----------
cust_id int null
name string Short name
state string null
# Partition Information
# col_name data_type comment
state string null
# Detailed Table Information
Database default
Table customer
Owner <TABLE OWNER>
Created Time Tue Apr 07 22:56:34 JST 2020
Last Access UNKNOWN
Created By <SPARK VERSION>
Type MANAGED
Provider parquet
Location file:/tmp/salesdb.db/custom...
Serde Library org.apache.hadoop.hive.ql.i...
InputFormat org.apache.hadoop.hive.ql.i...
OutputFormat org.apache.hadoop.hive.ql.i...
Partition Provider Catalog
-- Returns partition metadata such as partitioning column name, column type and comment.
> DESCRIBE TABLE EXTENDED customer PARTITION (state = 'AR');
col_name data_type comment
------------------------------ ------------------------------ ----------
cust_id int null
name string Short name
state string null
# Partition Information
# col_name data_type comment
state string null
# Detailed Partition Inform...
Database default
Table customer
Partition Values [state=AR]
Location file:/tmp/salesdb.db/custom...
Serde Library org.apache.hadoop.hive.ql.i...
InputFormat org.apache.hadoop.hive.ql.i...
OutputFormat org.apache.hadoop.hive.ql.i...
Storage Properties [serialization.format=1, pa...
Partition Parameters {transient_lastDdlTime=1586...
Created Time Tue Apr 07 23:05:43 JST 2020
Last Access UNKNOWN
Partition Statistics 659 bytes
# Storage Information
Location file:/tmp/salesdb.db/custom...
Serde Library org.apache.hadoop.hive.ql.i...
InputFormat org.apache.hadoop.hive.ql.i...
OutputFormat org.apache.hadoop.hive.ql.i...
------------------------------ ------------------------------ ----------
-- Returns the metadata for `name` column.
-- Optional `TABLE` clause is omitted and column is fully qualified.
> DESCRIBE customer salesdb.customer.name;
info_name info_value
--------- ----------
col_name name
data_type string
comment Short name
- Returns the table metadata in JSON format.
> DESCRIBE EXTENDED customer AS JSON;
{
"table_name":"customer",
"catalog_name":"spark_catalog",
"schema_name":"default",
"namespace":["default"],
"columns":[
{"name":"cust_id","type":{"name":"integer"},"nullable":true},
{"name":"name","type":{"name":"string"},"comment":"Short name","nullable":true},
{"name":"state","type":{"name":"varchar","length":20},"nullable":true}],
"location": "file:/tmp/salesdb.db/custom...",
"created_time":"2020-04-07T14:05:43Z",
"last_access":"UNKNOWN",
"created_by":"None",
"type":"MANAGED",
"provider":"parquet",
"partition_provider":"Catalog",
"partition_columns":["state"]}
-- The JSON describe of a metric view
> DESCRIBE EXTENDED region_sales_metrics AS JSON;
{
"table_name":"region_sales_metrics",
"catalog_name":"main",
"namespace":["default"],
"schema_name":"default",
"columns":[
{"name":"month","type":{"name":"timestamp_ltz"},"nullable":true},
{"name":"status","type":{"name":"string","collation":"UTF8_BINARY"},"nullable":true},
{"name":"prder_priority","type":{"name":"string","collation":"UTF8_BINARY"},"nullable":true},
{"name":"count_orders","type":{"name":"bigint"},"nullable":false,"is_measure":true},
{"name":"total_revenue","type":{"name":"decimal","precision":28,"scale":2},"nullable":true,"is_measure":true},
{"name":"total_revenue_per_customer","type":{"name":"decimal","precision":38,"scale":12},"nullable":true,"is_measure":true}],
"owner":"alf@melmak.et",
"created_time":"2025-05-18T23:45:25Z",
"last_access":"UNKNOWN",
"created_by":"Spark ",
"type":"METRIC_VIEW",
"comment":"A metric view for regional sales metrics.",
"view_text":"\n version: 0.1\n source: samples.tpch.orders\n filter: o_orderdate > '1990-01-01'\n dimensions:\n - name: month\n expr: date_trunc('MONTH', o_orderdate)\n - name: status\n expr: case\n when o_orderstatus = 'O' then 'Open'\n when o_orderstatus = 'P' then 'Processing'\n when o_orderstatus = 'F' then 'Fulfilled'\n end\n - name: prder_priority\n expr: split(o_orderpriority, '-')[1]\n measures:\n - name: count_orders\n expr: count(1)\n - name: total_revenue\n expr: SUM(o_totalprice)\n - name: total_revenue_per_customer\n expr: SUM(o_totalprice) / count(distinct o_custkey)\n ","language":"YAML","table_properties":{"metric_view.from.name":"samples.tpch.orders","metric_view.from.type":"ASSET","metric_view.where":"o_orderdate > '1990-01-01'"},
"view_creation_spark_configuration":{ ... },
"collation":"UTF8_BINARY"}
DESCRIBE DETAIL
DESCRIBE DETAIL [schema_name.]table_name
Return information about schema, partitioning, table size, and so on. For example, for Delta tables, you can see the current reader and writer versions of a table. See Review table details with describe detail for the detail schema.