SHOW TBLPROPERTIES (Databricks SQL)

Returns the value of a table property given an optional value for a property key. If no key is specified then all the properties are returned.

Syntax 

SHOW TBLPROPERTIES table_name
   [ ( [unquoted_property_key | property_key_as_string_literal] ) ]

unquoted_property_key
  key_part1 [. ...]

Parameters

  • table_name

    Identifies the table. The name must not include a temporal specification.

  • unquoted_property_key

    The property key in unquoted form. The key can consist of multiple parts separated by a dot.

  • property_key_as_string_literal

    A property key value as a string literal.

Note

Property value returned by this statement excludes some properties that are internal to spark and hive. The excluded properties are:

  • All the properties that start with prefix spark.sql
  • Property keys such as: EXTERNAL, comment
  • All the properties generated internally by hive to store statistics. Some of these properties are: numFiles, numPartitions, numRows.

Examples 

-- create a table `customer` in database `salesdb`
USE salesdb;
CREATE TABLE customer(cust_code INT, name VARCHAR(100), cust_addr STRING)
    TBLPROPERTIES ('created.by.user' = 'John', 'created.date' = '01-01-2001');

-- show all the user specified properties for table `customer`
SHOW TBLPROPERTIES customer;
+---------------------+----------+
|                  key|     value|
+---------------------+----------+
|      created.by.user|      John|
|         created.date|01-01-2001|
|transient_lastDdlTime|1567554931|
+---------------------+----------+

-- show all the user specified properties for a qualified table `customer`
-- in database `salesdb`
SHOW TBLPROPERTIES salesdb.customer;
+---------------------+----------+
|                  key|     value|
+---------------------+----------+
|      created.by.user|      John|
|         created.date|01-01-2001|
|transient_lastDdlTime|1567554931|
+---------------------+----------+

-- show value for unquoted property key `created.by.user`
SHOW TBLPROPERTIES customer (created.by.user);
+-----+
|value|
+-----+
| John|
+-----+

-- show value for property `created.date`` specified as string literal
SHOW TBLPROPERTIES customer ('created.date');
+----------+
|     value|
+----------+
|01-01-2001|
+----------+