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

• EXTENDED

  If 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).

• table_name

  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.

• PARTITION clause

  An optional parameter directing Databricks SQL to return addition metadata for the named partitions.

• column_name

  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 JSON

  Applies to: Databricks SQL Databricks Runtime 16.2 and above

  Optionally 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 EXTENDED format 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>",
      "is_measure": <boolean>, // Included only for measure columns of metric views
    }
  ],
  "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>"],
  "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 DBR 16.4 and above
  "language": "<language>" // Language such as YAML, included only for metric views
}

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", "collation": "<collation>" }
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

SQL

-- 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 Delta Lake table details with describe detail for the detail schema.

Related articles

• DESCRIBE FUNCTION
• DESCRIBE QUERY
• DESCRIBE SCHEMA
• INFORMATION_SCHEMA.COLUMNS
• INFORMATION_SCHEMA.TABLES
• PARTITION