SHOW TBLPROPERTIES
Applies to: Databricks SQL Databricks Runtime
Returns the value of a table property given an optional value for
a property key. If no key is specified then all the properties and options are returned.
Table options are prefixed with option
.
Syntax
SHOW TBLPROPERTIES table_name
[ ( [unquoted_property_key | property_key_as_string_literal] ) ]
unquoted_property_key
key_part1 [. ...]
Parameters
-
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 schema `salessc`
> USE salessc;
> 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 schema `salessc`
> SHOW TBLPROPERTIES salessc.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