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.
Important
Use DESCRIBE AS JSON to parse describe output programmatically. The non-JSON report format is subject to change.
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).
-
Identifies the table to be described. The name may not use a temporal specification or options specification. If the table cannot be found Azure 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 Runtime 16.2 and laterOptionally 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.
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>"
}
],
"partition_values": {
"<col_name>": "<val>"
},
"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>"],
"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>"
}
Below are the schema definitions for <type_json>:
| SQL Type | JSON Representation |
|---|---|
| TINYINT | { "name" : "tinyint" } |
| SMALLINT | { "name" : "smallint" } |
| INT | { "name" : "int" } |
| BIGINT | { "name" : "bigint" } |
| FLOAT | { "name" : "float" } |
| DOUBLE | { "name" : "double" } |
| DECIMAL(p, s) | { "name" : "decimal", "precision": p, "scale": s } |
| STRING | { "name" : "string" } |
| VARCHAR(n) | { "name" : "varchar", "length": n } |
| CHAR(n) | { "name" : "char", "length": n } |
| BINARY | { "name" : "binary" } |
| BOOLEAN | { "name" : "boolean" } |
| DATE | { "name" : "date" } |
| TIMESTAMP | { "name" : "timestamp_ltz" } |
| TIMESTAMP_NTZ | { "name" : "timestamp_ntz" } |
| INTERVAL start_unit TO end_unit | { "name" : "interval", "start_unit": "<start_unit>", "end_unit": "<end_unit>" } |
| ARRAY<element_type> | { "name" : "array", "element_type": <type_json>, "element_nullable": <boolean_val> } |
| MAP<key_type, value_type> | { "name" : "map", "key_type": <type_json>, "value_type": <type_json>, "element_nullable": <boolean_val> } |
| STRUCT<field_name …, …> | { "name" : "struct", "fields": [ {"name" : "<field_name>", "type" : <type_json>, “nullable”: <boolean_val>, "comment": “<field_comment>”, "default": “<default_val>”}] } |
| VARIANT | { "name" : "variant" } |
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"]}
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 Delta Lake table details with describe detail for the detail schema.