Skip to main content
Last updated on

SHOW CREATE TABLE

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns the statement that was used to create a given table or view. The returned statement can be any of the following types:

SHOW CREATE TABLE on a temporary view or non-existent table throws an exception.

Syntax​

SHOW CREATE TABLE { table_name | view_name }

Parameters​

Examples​

SQL
> CREATE TABLE test (c INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    STORED AS TEXTFILE
    TBLPROPERTIES ('prop1' = 'value1', 'prop2' = 'value2');

> SHOW CREATE TABLE test;
                                       createtab_stmt
 ----------------------------------------------------
 CREATE TABLE `default`.`test` (`c` INT)
 USING text
 TBLPROPERTIES (
   'transient_lastDdlTime' = '1586269021',
   'prop1' = 'value1',
   'prop2' = 'value2')

The following example shows SHOW CREATE TABLE for a metric view. The output captures the original definition and any subsequent ALTER VIEW changes, and the view name is returned as a fully qualified three-part name.

SQL
> CREATE OR REPLACE VIEW sample_sales_metrics_v2
    COMMENT 'Sales metrics for product and regional analysis'
    TBLPROPERTIES ('created_by' = 'Evan')
    WITH METRICS
    LANGUAGE YAML
    AS $$
  version: 1.1
  source: sample_sales
  comment: "Sales metrics for product and regional analysis"

  dimensions:
    - name: product_category
      expr: product_category
      comment: "Category of the product sold"
    - name: region
      expr: region
  measures:
    - name: total_sales
      expr: SUM(sales_amount)
      comment: "Sum of all sales amounts"
    - name: total_quantity
      expr: SUM(quantity)
      comment: "Total number of items sold"
  $$;

> ALTER VIEW sample_sales_metrics_v2
    SET TBLPROPERTIES ('purpose' = 'For testing');

> SHOW CREATE TABLE sample_sales_metrics_v2;
                              createtab_stmt
 ------------------------------------------------------------
 CREATE VIEW main.default.sample_sales_metrics_v2 (
   product_category COMMENT 'Category of the product sold',
   region,
   total_sales COMMENT 'Sum of all sales amounts',
   total_quantity COMMENT 'Total number of items sold')
 COMMENT 'Sales metrics for product and regional analysis'
 TBLPROPERTIES (
   'created_by' = 'Evan',
   'purpose' = 'For testing')
 WITH METRICS
 LANGUAGE YAML
 AS
 $$
 version: 1.1

 source: sample_sales

 comment: Sales metrics for product and regional analysis

 dimensions:
   - name: product_category
     expr: product_category
     comment: Category of the product sold
   - name: region
     expr: region

 measures:
   - name: total_sales
     expr: SUM(sales_amount)
     comment: Sum of all sales amounts
   - name: total_quantity
     expr: SUM(quantity)
     comment: Total number of items sold
 $$
On this page