SHOW statements provide a way to query/access the Hive metastore for existing data. The below are the list of SHOW options available to trigger on Metastore.

• Show Databases/Schemas
• Show Tables/Partitions/Indexes
  • Show Tables
  • Show Partitions
  • Show Table/Partition Extended
  • Show Table Properties
  • Show Create Table
  • Show Indexes
• Show Columns
• Show Functions

Show Database/Schemas -

SHOW DATABASES or SHOW SCHEMAS lists/displays all of the databases defined in the metastore. The database and schema are interchangeable. LIKE is an optional keyword in SHOW.

The LIKE clause allows the list of databases to be filtered using a regular expression which can be created using wildcards. Wildcards can only be '*' for any character(s) or '|' for a choice.

SHOW (DATABASES|SCHEMAS) [LIKE 
 'identifier_with_wildcards'];

Show Tables -

SHOW TABLES lists/displays all the base tables and views. SHOW TABLES list/displays tables from the current database. IN is an optional keyword in SHOW. The IN clause is used to provide the database if it is not the current.

SHOW TABLES can display tables with names matching the optional regular expression. Wildcards can only be '*' for any character(s) or '|' for a choice. Matching tables are listed in alphabetical order.

It is not an error if there are no matching tables found in metastore. If no regular expression is given then all tables in the selected database are listed.

SHOW TABLES [IN database_name]
 ['identifier_with_wildcards'];

Show Partitions:

SHOW PARTITIONS lists/displays all the existing partitions for a given base table. Partitions are listed in alphabetical order.

As of Hive 0.6, SHOW PARTITIONS can filter the list of partitions as shown below. It is also possible to specify parts of a partition specification to filter the resulting list.

SHOW PARTITIONS table_name;

Show Table Properties (Version: Hive 0.10.0):

SHOW TABLE PROPERTIES lists all of the table properties for the table. This is the first form in the syntax. The second form prints/displays the value for the property specified.

SHOW TBLPROPERTIES tblname;
SHOW TBLPROPERTIES tblname("foo");

Show Create Table/View (Version: Hive 0.10.0):

SHOW CREATE TABLE shows the CREATE TABLE statement used to create the given table. SHOW CREATE VIEW shows the CREATE VIEW statement used to create the given view.

SHOW CREATE TABLE ([db_name.]table_name|view_name);

Show Indexes (Version: Hive 0.7.):

SHOW INDEXES shows/displays all of the indexes on the column provided. SHOW INDEXES also shows the information like index name, table name, names of the columns used as keys, index table name, index type and comment. FORMATTED is optional keyword. If the FORMATTED keyword is used, then column titles are printed for each column.

SHOW [FORMATTED] (INDEX|INDEXES) ON 
 table_with_index [(FROM|IN) db_name];

Show Columns (Version: Hive 0.10.0):

SHOW COLUMNS shows/displays all the columns in a table including partition columns.

SHOW COLUMNS (FROM|IN) table_name
 [(FROM|IN) db_name];

Show Functions:

SHOW FUNCTIONS lists/displays all the user defined and builtin functions matching the regular expression. use ".*" to get all functions details.

SHOW FUNCTIONS ".*";