SHOW
SHOW — display information about select database objects and states
Syntax
SHOW
SHOW { TABLES | DOMAINS | SEQUENCES | PROCEDURES | FUNCTIONS }
[ { FROM | IN } [SCHEMA] schema_name ]
[ LIKE '`string_literal`' ]
SHOW { TABLE | DOMAIN | SEQUENCE | PROCEDURE | FUNCTION }
[schema_name.]object_name
SHOW PARTITIONS FOR TABLE [schema_name.]table_name
SHOW SCHEMA [ schema_name ]
SHOW SCHEMAS [ LIKE '`string_literal`' ]
SHOW DATABASE
SHOW object_name
SHOW AUTOCOMMITDescription
SHOW displays information about schemas, tables, domains, sequences, procedures, and user defined functions.
SHOW will also return partial matches in the above categories.
You can also use SHOW to determine the status of the setting for AUTOCOMMIT and other useful settings in nuosql.
By default SHOW uses the current schema, if none is specified. SHOW commands do not require a semi-colon to execute.
If NuoDB cannot find a table, domain, sequence or procedure with the exact name that is specified, it searches for database objects beginning with the characters in the specified object_name, in the current or specified schema_name.
SHOW DATABASE will show version number information about your database and all the nodes running in your database. SHOW with no arguments will show information about your database and your client session including any system defined properties.
The number of partitions is also displayed for partitioned tables. Additional information about the partitions can be displayed using the SHOW PARTITIONS FOR TABLE command, see Example 3 below.
Parameters
schema_name-
The name of the schema.
table_name-
The name of the table.
object_name-
The name of the database object.
string_literal-
A search string. This must be entered as uppercase, followed by a percent (%). This is similar syntax used by the
LIKEpattern.
Examples
- Example 1: Show all the different
SHOWcommands and the displayed output from various schemas. -
/* the current SCHEMA is schema2 */ USE schema2; SHOW Database Settings: database name is test database version is xxxxxxx <--- (where xxxxxxx shows the database product release version) current schema is SCHEMA2 autocommit state set to ON allow autocommit in stored procedures is not supported query timeout is set to 0 microseconds transaction isolation level set to SERIALIZABLE or REPEATABLE READ stored procedure rollback mode is NOT IMPLEMENTED Vectorized Execution Engine System Properties: ATOM_GC_LOCK_TIMEOUT: 10000 AUTOCOMMIT_DDL: FALSE COMPAT_CARDINALITY_CLAMP: FALSE DB_TRACE: OFF DB_TRACE_MIN_TIME: 0.000000 DB_TRACE_PATTERN: DB_TRACE_PROCEDURES: DB_TRACE_TABLE: TRACESCHEMA.TRACETABLE DEFAULT_CONNECTION_MEMORY_LIMIT: 0.000000 DISKSPILL_MEMORY_THRESHOLD_MB: 64 ENFORCE_TABLE_LOCKS_FOR_DDL: FALSE IDLE_CONNECTION_TIMEOUT: 0 INDEX_ENCODING: -1 MAX_CONNECTION_OPEN_RESULTSETS: 1000 MAX_CONNECTION_OPEN_STATEMENTS: 1000 MAX_MATERIALIZED_QUERY_SIZE: 67108864 MAX_QUERY_COUNT: 10 MAX_STMT_CACHE_SIZE: 500 MIN_QUERY_TIME: 10.000000 MIN_QUERY_WARN_TIME: 315360000.000000 OPT_MAX_SCAN_REOPTIMIZE: 20 OPT_REMOVE_INDEXED_PRED: ALL OPT_STATS_INDEX: TRUE ROLLBACK_DEADLOCK_VICTIM: TRUE SKIP_UNCOMMITTED_INSERTS: FALSE SQL_DISKSPILL: TRUE SQL_STATEMENT_LOG_MIN_TIME: 0.000000 TABLE_CHG_TOLERANCE: 0.100000 TRANSACTIONAL_DDL: TRUE TRANSACTIONAL_LOCKS: TRUE UDF_CACHE_SIZE: 50 USE_COVERING_INDEX: TRUE connection memory limit is 0 bytes scratch-dir is NOT AVAILABLE NuoSQL Settings: semicolon completion is required current command delimiter is [;] output will display horizontally pager: (not set) statement analyze set to OFF timer is set to ON connection trace is set to OFF /* no table named hockey owned by schema2 */ SHOW TABLE HOCKEY No tables found named HOCKEY /* no sequences owned by schema2 */ SHOW SEQUENCES No sequences found in schema SCHEMA2 Sequences in all schemas HOCKEY$IDENTITY_SEQUENCE in schema HOCKEY SHOW SCHEMA hockey Tables in schema HOCKEY HOCKEY PLAYERS SCORING TEAMS VW_PLAYER_STATS is a view USE SCHEMA1 CREATE TABLE tab_schema1(column1 INTEGER); USE SCHEMA2 CREATE TABLE tab_schema2(column1 INTEGER); SHOW SCHEMAS LIKE 'SCHEMA%'; Found 2 schemas SCHEMA1 SCHEMA2 (current) USE HOCKEY SHOW hockey Tables named HOCKEY Found table HOCKEY in schema HOCKEY Fields: ID bigint Nullable: No Generator: HOCKEY$IDENTITY_SEQUENCE Generated Always NUMBER integer NAME string POSITION string TEAM string Primary Index: HOCKEY..PRIMARY_KEY on field: ID Unique Index: PLAYER_IDX on fields: NUMBER, NAME, TEAM No sequences found with name HOCKEY No domains found named HOCKEY No procedures found named HOCKEY No functions found named HOCKEY /* Show a table with all indexes and foreign keys */ SHOW TABLE hockey.scoring; Tables named SCORING Found table SCORING in schema HOCKEY Fields: PLAYERID varchar(10) Nullable: No YEAR integer Nullable: No STINT integer Nullable: No TEAMID varchar(3) Nullable: No POSITION varchar(4) Nullable: No GAMESPLAYED integer Nullable: No Default: 0 GOALS integer Nullable: No Default: 0 ASSISTS integer Nullable: No Default: 0 PENALTYMINUTES integer Nullable: No Default: 0 Primary Index: SCORING..PRIMARY_KEY on fields: PLAYERID, YEAR, STINT, TEAMID, POSITION Foreign Key FK_HOCKEY_SCORING_PLAYERID_#_HOCKEY_PLAYERS_PLAYERID: (PLAYERID) references PLAYERS(PLAYERID) Foreign Key FK_HOCKEY_SCORING_YEAR_TEAMID_#_HOCKEY_TEAMS_YEAR_TEAMID: (YEAR,TEAMID) references TEAMS(YEAR,TEAMID) /* Create a function */ DROP FUNCTION IF EXISTS func_is_date; SET DELIMITER @ CREATE FUNCTION func_is_date (i_date string) RETURNS BOOLEAN DETERMINISTIC LANGUAGE SQL SECURITY INVOKER AS VAR l_out BOOLEAN = 'TRUE'; VAR l_timestamp TIMESTAMP; try l_timestamp = (SELECT DATE(i_date) FROM DUAL); catch(error) l_out = 'FALSE'; end_try; RETURN l_out; END_FUNCTION; @ SET DELIMITER ; /* Show function */ SHOW FUNCTIONS Functions in schema HOCKEY FUNC_IS_DATE SHOW FUNCTION func_is_date Functions found in schema HOCKEY Function FUNC_IS_DATE [RETURN] boolean [IN] I_DATE string [IN] I_FORMAT string SHOW FUNCTIONS LIKE 'FUNC%' Functions in schema HOCKEY FUNC_IS_DATE - Example 2:
SHOW DATABASE -
The example below shows
SHOW DATABASEoutput in a domain with one Transaction Engine (TE) and one Storage Manager (SM). The database name istest, it is Version 2.4.1. Each node running is the current release and running in the default region.SHOW DATABASE Database: test Version: xxxx <--- where xxxx is the current database product version Nodes: SM 1 127.0.0.1:63164 (Running) NONE xxx <--- where xxx is the product version TE 2 127.0.0.1:63166 (Running) NONE xxx <--- where xxx is the product version - Example 3:
SHOW PARTITIONS FOR TABLE -
An example using a partitioned table and the output from
SHOW PARTIIONS FROM TABLE.CREATE TABLE test_table (x INTEGER, y STRING) PARTITION BY RANGE (x) (PARTITION p1 VALUES LESS THAN (10) store in stg1 PARTITION p2 VALUES LESS THAN (20) STORE IN stg2 PARTITION p3 VALUES LESS THAN (MAXVALUE) STORE IN stg1); SHOW TABLE test_table; Tables named TEST_TABLE Found table TEST_TABLE in schema USER Fields: X integer Y string Partitioned by Range on field X with 3 existing partitions SHOW PARTITIONS FOR TABLE test_table; Tables named TEST_TABLE Found table TEST_TABLE in schema USER Partitioned by Range on field X P1 values less than (10) in Storage Group STG1 P2 values less than (20) in Storage Group STG2 P3 default in Storage Group STG1