0001 ---
0002 layout: global
0003 title: SHOW TBLPROPERTIES
0004 displayTitle: SHOW TBLPROPERTIES
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 This statement returns the value of a table property given an optional value for
0025 a property key. If no key is specified then all the properties are returned.
0026
0027 ### Syntax
0028
0029 ```sql
0030 SHOW TBLPROPERTIES table_identifier
0031 [ ( unquoted_property_key | property_key_as_string_literal ) ]
0032 ```
0033
0034 ### Parameters
0035
0036 * **table_identifier**
0037
0038 Specifies the table name of an existing table. The table may be optionally qualified
0039 with a database name.
0040
0041 **Syntax:** `[ database_name. ] table_name`
0042
0043 * **unquoted_property_key**
0044
0045 Specifies the property key in unquoted form. The key may consists of multiple
0046 parts separated by dot.
0047
0048 **Syntax:** `[ key_part1 ] [ .key_part2 ] [ ... ]`
0049
0050 * **property_key_as_string_literal**
0051
0052 Specifies a property key value as a string literal.
0053
0054 **Note**
0055 - Property value returned by this statement excludes some properties
0056 that are internal to spark and hive. The excluded properties are :
0057 - All the properties that start with prefix `spark.sql`
0058 - Property keys such as: `EXTERNAL`, `comment`
0059 - All the properties generated internally by hive to store statistics. Some of these
0060 properties are: `numFiles`, `numPartitions`, `numRows`.
0061
0062 ### Examples
0063
0064 ```sql
0065 -- create a table `customer` in database `salesdb`
0066 USE salesdb;
0067 CREATE TABLE customer(cust_code INT, name VARCHAR(100), cust_addr STRING)
0068 TBLPROPERTIES ('created.by.user' = 'John', 'created.date' = '01-01-2001');
0069
0070 -- show all the user specified properties for table `customer`
0071 SHOW TBLPROPERTIES customer;
0072 +---------------------+----------+
0073 | key| value|
0074 +---------------------+----------+
0075 | created.by.user| John|
0076 | created.date|01-01-2001|
0077 |transient_lastDdlTime|1567554931|
0078 +---------------------+----------+
0079
0080 -- show all the user specified properties for a qualified table `customer`
0081 -- in database `salesdb`
0082 SHOW TBLPROPERTIES salesdb.customer;
0083 +---------------------+----------+
0084 | key| value|
0085 +---------------------+----------+
0086 | created.by.user| John|
0087 | created.date|01-01-2001|
0088 |transient_lastDdlTime|1567554931|
0089 +---------------------+----------+
0090
0091 -- show value for unquoted property key `created.by.user`
0092 SHOW TBLPROPERTIES customer (created.by.user);
0093 +-----+
0094 |value|
0095 +-----+
0096 | John|
0097 +-----+
0098
0099 -- show value for property `created.date`` specified as string literal
0100 SHOW TBLPROPERTIES customer ('created.date');
0101 +----------+
0102 | value|
0103 +----------+
0104 |01-01-2001|
0105 +----------+
0106 ```
0107
0108 ### Related Statements
0109
0110 * [CREATE TABLE](sql-ref-syntax-ddl-create-table.html)
0111 * [ALTER TABLE SET TBLPROPERTIES](sql-ref-syntax-ddl-alter-table.html)
0112 * [SHOW TABLES](sql-ref-syntax-aux-show-tables.html)
0113 * [SHOW TABLE EXTENDED](sql-ref-syntax-aux-show-table.html)
