SHOW

Owned by Boaz Raufman
Apr 02, 2023
3 min read

Displays the metadata information of the instance.

Syntax

SHOW PARAM parameter_name | ALL 
SHOW PARAM EXTENDED parameter | ALL 
SHOW [FULL] SCHEMAS 
SHOW TABLES [EXTENDED | MAINT]  [IN schema_name] 
SHOW TABLE EXTENDED [schema_name.]table-name
SHOW TABLE COLUMNS | PARTITIONS [schema_name.]table_name 
SHOW TABLE PARTITIONS [schema_name.]table_name 
SHOW FULL TABLES [IN schema_name]
SHOW VIEWS [EXTENDED] [IN schema_name] 
SHOW LOCAL CACHE
SHOW ADAPTIVE CACHE
SHOW ACTIVE QUERIES 
SHOW JOIN INDEXES [IN schema_name]
SHOW RANGE INDEXES [IN schema_name]
SHOW SCHEDULED LOADS [IN schema_name]
SHOW VERSION 
SHOW CUBES 
SHOW CUBES BY TAG
SHOW CUBES FROM <select-statement>
SHOW GROUP BY STATISTICS
SHOW ADAPTIVE CACHE ACTIVE
SHOW ROLES
SHOW CURRENT ROLES
SHOW ROLES GRANT GROUP group_name
SHOW GRANT ROLE role_name
SHOW GRANT ON [ALL | SCHEMA schema_name | TABLE [schema_name.]table_name | VIEW view_name]
SHOW LDAP GROUPS
SHOW EXTERNAL DATA SOURCES
SHOW EXTERNAL DATA SOURCE datasource-name
SHOW EXTERNAL TABLES [IN schema_name]
SHOW EXTERNAL TABLE [schema_name.]table-name
SHOW EXTERNAL TABLES COLUMNS [IN schema_name]
SHOW EXTERNAL TABLE COLUMNS [schema_name.]table-name
SHOW TABLES STATS [IN schema_name] 
SHOW TABLE STATS  [schema_name.]table_name 
SHOW CREATE TABLE [schema_name.]{table_name|view_name}

Description

Displays various aspects of the system. Some of the command options allow displaying detailed storage usage data, for example at the table, partition, or cache level. Displaying low-resolution storage usage data may take a while, as those commands analyze the current storage usage.

  • SHOW PARAM – Displays the current value of a specific system parameter, or all Output specifies set value and set level using "*" sign.
  • SHOW PARAM EXTENDED - Displays both the local and global current values, next to the default value for each parameter.
  • SHOW SCHEMAS – Lists all schemas defined in the Currently it is not possible to add new schemas to the default schema.
  • SHOW TABLES – Lists all tables in the instance's current active schema.
  • SHOW TABLES EXTENDED – Lists all tables with more detailed information, including number of columns, number of rows, number of partitions, and size on disk.
  • SHOW TABLE EXTENDED table-name - Lists detailed information about a specific table, including number of columns, number of rows, number of partitions, and size on disk.
  • SHOW TABLES MAINT – Lists the background maintenance status of all tables.
  • SHOW TABLE COLUMNS – Lists all columns of a table, including number of distinct values, number of NULLs and size on disk.
  • SHOW TABLE PARTITIONS – Lists all partitions of a partitioned table, including partition boundaries, number of rows, and size on disk.
  • SHOW FULL TABLES - Lists all tables in the instance's current active schema, with schema information.
  • SHOW VIEWS [EXTENDED] – Lists all views in the instance or in a specific schema. When specifying EXTENDED, also lists the view status (valid/invalid) and underlying SQL statement.
  • SHOW LOCAL CACHE – Lists the contents of the local cache – a summary per table.
  • SHOW ADAPTIVE CACHE - Lists all the queries which are stored in cache, and aggregated information about them.
  • SHOW ACTIVE QUERIES – Lists all in-progress queries (running or queued).
  • SHOW JOIN INDEXES – Lists all existing join indexes and their attributes.
  • SHOW RANGE INDEXES – Lists all existing range indexes and their attributes.
  • SHOW SCHEDULED LOADS - Displays all scheduled loads.
  • SHOW VERSION – Shows the server's version number.
  • SHOW CUBES – Displays all generated cubes.
  • SHOW CUBES BY TAG - Displays cubes information for a given cube tag.
  • SHOW CUBES {WITH WHERE <where-clause>} FROM <select-statement> - Lists all existing and potentials cubes for a given query.
  • SHOW GROUP BY STATISTICS - Displays row count info for all cubes/candidates per group-by entries.
  • SHOW ADAPTIVE CACHE – Shows all adaptive cache entries.
  • SHOW ROLES - Shows all available roles. 
  • SHOW CURRENT ROLES - Shows only the roles applied for the current user.
  • SHOW ROLES GRANT <group-name> - Shows all the roles, for a specific group.
  • SHOW ROLES GRANT GROUPS - Shows all the roles, for all the groups.
  • SHOW GRANT ROLE <role-name> - Lists the permissions that were granted to a specified role.
  • SHOW GRANT ON {<object> | ALL} - Shows the roles and permissions which are granted to an object (SCHEMA/TABLE/VIEW name). The command: “SHOW GRANT ON ALL” will show all the roles and permissions for all of the objects of the current instance.
  • SHOW LDAP GROUPS - Shows all the groups which are defined in the LDAP.
  • SHOW EXTERNAL DATA SOURCES -  Shows all the attributes of all external data source.
  • SHOW EXTERNAL DATA SOURCE <datasource-name> - Shows all the attributes of a specific external data source.
  • SHOW EXTERNAL TABLES - Shows all the attributes of all external tables.
  • SHOW EXTERNAL TABLE <table-name> - Shows all the attributes of a specific external table.
  • SHOW EXTERNAL TABLES COLUMNS - Lists all the columns of all external tables, including number of distinct values, number of NULLs and size on disk.
  • SHOW EXTERNAL TABLE COLUMNS <table-name> - Lists all the columns of a specific table, including number of distinct values, number of NULLs and size on disk.
  • SHOW TABLES STATS [IN schema_name] - Shows quick table metadata information, for all the tables in a specified schema.
  • SHOW TABLE STATS [schema_name.]table_name - Shows quick table metadata information, for a specified table.
  • SHOW CREATE TABLE [schema_name.]{table_name|view_name} - Shows the CREATE TABLE or CREATE VIEW statement that created the given exiting table/view.