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 SCHEMA [ schema_name ]
SHOW SCHEMAS [ LIKE '`string_literal`' ]
SHOW DATABASE
SHOW object_name
SHOW AUTOCOMMIT

Description

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.

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 upper case, followed by a percent (%). This is similar syntax used by the LIKE pattern.

Examples

Example 1: Show all the different SHOW commands 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 OFF
        query timeout is not set
        transaction isolation level set to SERIALIZABLE or REPEATABLE READ
        stored procedure rollback mode is PROCEDURE
        query graph setting is FORCE
        Scalar Execution Engine

        System Properties:
                    MAX_QUERY_COUNT:                   10
                     MIN_QUERY_TIME:            10.000000
                MIN_QUERY_WARN_TIME:     315360000.000000
                    OPT_STATS_INDEX:                 TRUE
                MAX_STMT_CACHE_SIZE:                  500
                TABLE_CHG_TOLERANCE:             0.100000
     MAX_CONNECTION_OPEN_RESULTSETS:                 1000
     MAX_CONNECTION_OPEN_STATEMENTS:                 1000
                     INDEX_ENCODING:                   -1
            OPT_MAX_SCAN_REOPTIMIZE:                   20
           SKIP_UNCOMMITTED_INSERTS:                FALSE
                     UDF_CACHE_SIZE:                   50
           COMPAT_CARDINALITY_CLAMP:                FALSE
        MAX_MATERIALIZED_QUERY_SIZE:             67108864
                           DB_TRACE:                  OFF
                     DB_TRACE_TABLE: TRACESCHEMA.TRACETABLE
                   DB_TRACE_PATTERN:
                  DB_TRACE_MIN_TIME:             0.000000
                DB_TRACE_PROCEDURES:
         SQL_STATEMENT_LOG_MIN_TIME:             0.000000
                  TRANSACTIONAL_DDL:                 TRUE
                 USE_COVERING_INDEX:                 TRUE
            OPT_REMOVE_INDEXED_PRED:                  ALL
    DEFAULT_CONNECTION_MEMORY_LIMIT:             0.000000
                TRANSACTIONAL_LOCKS:                 TRUE
           ROLLBACK_DEADLOCK_VICTIM:                 TRUE
        ENFORCE_TABLE_LOCKS_FOR_DDL:                FALSE
                      SQL_DISKSPILL:                 TRUE
      DISKSPILL_MEMORY_THRESHOLD_MB:                   64
            IDLE_CONNECTION_TIMEOUT:                    0
                     AUTOCOMMIT_DDL:                FALSE
               ATOM_GC_LOCK_TIMEOUT:                10000

    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 in schema SCHEMA2

    Sequences in all schemas
        HOCKEY.HOCKEY$IDENTITY_SEQUENCE

/* can show tables owned by another schema (hockey) */
SHOW TABLES FROM HOCKEY
    Tables in schema HOCKEY
        HOCKEY
        PLAYERS
        SCORING
        TEAMS
        VW_PLAYER_STATS is a view

SHOW TABLES FROM SCHEMA hockey LIKE 'PLA%'
    Tables in schema HOCKEY
        PLAYERS

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 with name 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 DATABASE output in a domain with one transaction engine (TE) and one storage manager (SM). The database name is test, 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