SHOW

SHOW TABLES

Displays the list of all table names within a database. The name of the result column will be tables_in_<database name> and it will have one column. If you use the LIKE clause, you can search the table names matching this and if you use the WHERE clause, you can search table names with more general terms. SHOW FULL TABLES displays the second column, table_type together. The table must have the value, BASE TABLE and the view has the value, VIEW.

SHOW [FULL] TABLES [LIKE 'pattern' | WHERE expr]

The following is the result of executing the query in the demodb database.

SHOW TABLES;

  Tables_in_demodb
======================
  'athlete'
  'code'
  'event'
  'game'
  'history'
  'nation'
  'olympic'
  'participant'
  'record'
  'stadium'

SHOW FULL TABLES;

  Tables_in_demodb     Table_type
============================================
  'athlete'             'BASE TABLE'
  'code'                'BASE TABLE'
  'event'               'BASE TABLE'
  'game'                'BASE TABLE'
  'history'             'BASE TABLE'
  'nation'              'BASE TABLE'
  'olympic'             'BASE TABLE'
  'participant'         'BASE TABLE'
  'record'              'BASE TABLE'
  'stadium'             'BASE TABLE'

SHOW FULL TABLES LIKE '%c%';

  Tables_in_demodb      Table_type
============================================
  'code'                'BASE TABLE'
  'olympic'             'BASE TABLE'
  'participant'         'BASE TABLE'
  'record'              'BASE TABLE'

SHOW FULL TABLES WHERE table_type = 'BASE TABLE' and TABLES_IN_demodb LIKE '%co%';

  Tables_in_demodb      Table_type
============================================
  'code'                'BASE TABLE'
  'record'              'BASE TABLE'

SHOW COLUMNS

Displays the column information of a table. You can use the LIKE clause to search the column names matching it. If you use the WHERE clause, you can search column names with more general terms like, "General Considerations for All SHOW Statements."  :

  • Field: Column name

  • Type: Column data type

  • Null: If you can store NULL, the value is YES and if not, it is NO

  • Key: Whether a column has an index or not. If there is more than one key value in the given column of a table, this displays only the one that appears first in the order of PRI, UNI and MUL.
    • If the key is a space, the column doesn't have an index, it is not the first column in the multiple column index or the index is non-unique.
    • If the value is PRI, it is a primary key or the primary key of multiple columns.
    • If the value is UNI, it is a unique index. (The unique index allows multiple NULL values but you can also set a NOT NULL constraint.)
    • If the value is MUL, it is the first column of the non-unique index that allows the given value to be displayed in the column several times. If the column composes a composite unique index, the value will be MUL. The combination of column values can be unique but the value of each column can appear several times.
  • Default : Default value defined in the column

  • Extra : Additional information available on the given column. For the column with AUTO_INCREMENT constraint, it shows the 'auto_increment'.

If a FULL keyword is used, it displays the additional information, collation.

SHOW FIELDS is the same statement as SHOW COLUMNS.

The DESCRIBE (abbreviated DESC) statement and the EXPLAIN statement provide similar information to SHOW COLUMNS.

SHOW [FULL] COLUMNS {FROM | IN} tbl_name [LIKE 'pattern' | WHERE expr]

The following is the result of a query in the demodb database.

SHOW COLUMNS FROM athlete;

  Field                 Type                  Null       Key          Default               Extra
================================================================================================================
  'code'                'INTEGER'             'NO'       'PRI'        NULL                  'auto_increment'
  'name'                'VARCHAR(40)'         'NO'       ''           NULL                  ''
  'gender'              'CHAR(1)'             'YES'      ''           NULL                  ''
  'nation_code'         'CHAR(3)'             'YES'      ''           NULL                  ''
  'event'               'VARCHAR(30)'         'YES'      ''           NULL                  ''

SHOW COLUMNS FROM athlete WHERE field LIKE '%c%';

  Field                 Type                  Null       Key          Default               Extra
================================================================================================================
  'code'                'INTEGER'             'NO'       'PRI'        NULL                  'auto_increment'
  'nation_code'         'CHAR(3)'             'YES'      ''           NULL                  ''

SHOW COLUMNS FROM athlete  WHERE "type" = 'INTEGER' and "key"='PRI' AND extra='auto_increment';

  Field                 Type                  Null       Key          Default               Extra
================================================================================================================
  'code'                'INTEGER'             'NO'       'PRI'        NULL                  'auto_increment'

SHOW COLUMNS FROM athlete WHERE field LIKE '%c%';

  Field                 Type                  Collation             Null      Key         Default               Extra
====================================================================================================================================
  'code'                'INTEGER'             NULL                  'NO'      'PRI'       NULL                  'auto_increment'
  'nation_code'         'CHAR(3)'             'iso88591_bin'        'YES'     ''          NULL                  ''

SHOW INDEX

The SHOW INDEX statement displays the index information. This query has the following columns:

  • Table: Table Name

  • Non_unique
    • 0: Duplicate data are not allowed
    • 1: Duplicate data are allowed
  • Key_name: Index name

  • Seq_in_index: Serial number of the column in the index. Starts from 1.

  • Column_name: Column name

  • Collation: Method of sorting columns in the index. 'A' means ascending and NULL means not sorted.

  • Cardinality: The number of values measuring the unique values in the index. Higher cardinality increases the opportunity of using an index. This value is updated every time SHOW INDEX is executed.

  • Sub_part: The number of bytes of the indexed characters if the columns are indexed partially. NULL if all columns are indexed.

  • Packed: Shows how keys are packed. If they are not packed, it will be NULL.

  • Null: YES if a column can include NULL, NO if not.

  • Index_type: Index to be used (currently, only the BTREE is supported.)

SHOW {INDEX | INDEXES | KEYS } {FROM | IN} tbl_name

The following is the result of a query in the demodb database.

SHOW INDEX IN athlete;

   Table     Non_unique   Key_name       Seq_in_index  Column_name    Collation     Cardinality   Sub_part  Packed   Null   Index_type
==========================================================================================================================================
 'athlete'     0      'pk_athlete_code'     1          'code'           'A'           6677         NULL     NULL    'NO'      'BTREE'

CREATE TABLE t1( i1 INTEGER , i2 INTEGER NOT NULL, i3 INTEGER UNIQUE, s1 VARCHAR(10), s2 VARCHAR(10), s3 VARCHAR(10) UNIQUE);

CREATE INDEX i_t1_i1 ON t1(i1 DESC);
CREATE INDEX i_t1_s1 ON t1(s1(7));
CREATE INDEX i_t1_i1_s1 ON t1(i1,s1);
CREATE UNIQUE INDEX i_t1_i2_s2 ON t1(i2,s2);

SHOW INDEXES FROM t1;

  Table  Non_unique  Key_name          Seq_in_index  Column_name   Collation   Cardinality     Sub_part    Packed   Null    Index_type
==========================================================================================================================================
  't1'           0  'i_t1_i2_s2'              1      'i2'          'A'            0               NULL        NULL     'NO'    'BTREE'
  't1'           0  'i_t1_i2_s2'              2      's2'          'A'            0               NULL        NULL     'YES'   'BTREE'
  't1'           0  'u_t1_i3'                 1      'i3'          'A'            0               NULL        NULL     'YES'   'BTREE'
  't1'           0  'u_t1_s3'                 1      's3'          'A'            0               NULL        NULL     'YES'   'BTREE'
  't1'           1  'i_t1_i1'                 1      'i1'          NULL           0               NULL        NULL     'YES'   'BTREE'
  't1'           1  'i_t1_i1_s1'              1      'i1'          'A'            0               NULL        NULL     'YES'   'BTREE'
  't1'           1  'i_t1_i1_s1'              2      's1'          'A'            0               NULL        NULL     'YES'   'BTREE'
  't1'           1  'i_t1_s1'                 1      's1'          'A'            0                  7        NULL     'YES'   'BTREE'

SHOW COLLATION

SHOW COLLATION statement lists collations supported by the database. If LIKE clause is present, it indicates which collation names to match. This query has the following columns:

  • Collation: Collation name
  • Charset: Charset name
  • Id: Collation ID
  • Built_in: Built-in collation or not. Built-in collations are impossible to add or remove because they are hard-coded.
  • Expansions: Collation with expansion or not. For details, see Expansion.
  • Strength: The number of levels that are to be considered in comparison, and the character order can be different by this number. For details, see Collation Properties.

The following shows SHOW COLLATION syntax and examples.

SHOW COLLATION [ LIKE 'pattern' ]
SHOW COLLATION;

  Collation             Charset                        Id  Built_in              Expansions            Strength
===========================================================================================================================
  'euckr_bin'           'euckr'                         8  'Yes'                 'No'                  'Not applicable'
  'iso88591_bin'        'iso88591'                      0  'Yes'                 'No'                  'Not applicable'
  'iso88591_en_ci'      'iso88591'                      3  'Yes'                 'No'                  'Not applicable'
  'iso88591_en_cs'      'iso88591'                      2  'Yes'                 'No'                  'Not applicable'
  'utf8_bin'            'utf8'                          1  'Yes'                 'No'                  'Not applicable'
  'utf8_de_exp'         'utf8'                         76  'No'                  'Yes'                 'Tertiary'
  'utf8_de_exp_ai_ci'   'utf8'                         72  'No'                  'Yes'                 'Primary'
  'utf8_en_ci'          'utf8'                          5  'Yes'                 'No'                  'Not applicable'
  'utf8_en_cs'          'utf8'                          4  'Yes'                 'No'                  'Not applicable'
  'utf8_es_cs'          'utf8'                         85  'No'                  'No'                  'Quaternary'
  'utf8_fr_exp_ab'      'utf8'                         94  'No'                  'Yes'                 'Tertiary'
  'utf8_gen'            'utf8'                         32  'No'                  'No'                  'Quaternary'
  'utf8_gen_ai_ci'      'utf8'                         37  'No'                  'No'                  'Primary'
  'utf8_gen_ci'         'utf8'                         44  'No'                  'No'                  'Secondary'
  'utf8_ja_exp'         'utf8'                        124  'No'                  'Yes'                 'Tertiary'
  'utf8_ja_exp_cbm'     'utf8'                        125  'No'                  'Yes'                 'Tertiary'
  'utf8_km_exp'         'utf8'                        132  'No'                  'Yes'                 'Quaternary'
  'utf8_ko_cs'          'utf8'                          7  'Yes'                 'No'                  'Not applicable'
  'utf8_ko_cs_uca'      'utf8'                        133  'No'                  'No'                  'Quaternary'
  'utf8_tr_cs'          'utf8'                          6  'Yes'                 'No'                  'Not applicable'
  'utf8_tr_cs_uca'      'utf8'                        205  'No'                  'No'                  'Quaternary'
  'utf8_vi_cs'          'utf8'                        221  'No'                  'No'                  'Quaternary'

SHOW COLLATION LIKE '%_ko_%';

  Collation             Charset                        Id  Built_in              Expansions            Strength
===========================================================================================================================
  'utf8_ko_cs'          'utf8'                          7  'Yes'                 'No'                  'Not applicable'
  'utf8_ko_cs_uca'      'utf8'                        133  'No'                  'No'                  'Quaternary'

SHOW GRANTS

The SHOW GRANT statement displays the permissions associated with the database user accounts.

SHOW GRANTS FOR 'user'
CREATE TABLE testgrant (id INT);
CREATE USER user1;
GRANT INSERT,SELECT ON testgrant TO user1;

SHOW GRANTS FOR user1;

  Grants for USER1
======================
  'GRANT INSERT, SELECT ON testgrant TO USER1'

SHOW CREATE TABLE

When a table name is specified, the SHOW CREATE TABLE statement outputs the CREATE TABLE statement of the table.

SHOW CREATE TABLE table_name
SHOW CREATE TABLE nation;

  TABLE                 CREATE TABLE
============================================
  'nation'              'CREATE TABLE [nation] ([code] CHARACTER(3) NOT NULL, [name] CHARACTER VARYING(40) NO
T NULL, [continent] CHARACTER VARYING(10), [capital] CHARACTER VARYING(30),  CONSTRAINT [pk_nation_code] PRIM
ARY KEY  ([code]))'

SHOW CREATE VIEW

The SHOW CREATE VIEW statement outputs the corresponding CREATE VIEW statement if view name is specified.

SHOW CREATE VIEW view_name

The following example shows the result of executing query in the demodb database.

SHOW CREATE VIEW db_class;

  View              Create View
========================================
  'db_class'       'SELECT c.class_name, CAST(c.owner.name AS VARCHAR(255)), CASE c.class_type WHEN 0 THEN 'CLASS' WHEN 1 THEN 'VCLASS' ELSE
                   'UNKNOW' END, CASE WHEN MOD(c.is_system_class, 2) = 1 THEN 'YES' ELSE 'NO' END, CASE WHEN c.sub_classes IS NULL THEN 'NO'
                   ELSE NVL((SELECT 'YES' FROM _db_partition p WHERE p.class_of = c and p.pname IS NULL), 'NO') END, CASE WHEN
                   MOD(c.is_system_class / 8, 2) = 1 THEN 'YES' ELSE 'NO' END FROM _db_class c WHERE CURRENT_USER = 'DBA' OR {c.owner.name}
                   SUBSETEQ (  SELECT SET{CURRENT_USER} + COALESCE(SUM(SET{t.g.name}), SET{})  FROM db_user u, TABLE(groups) AS t(g)  WHERE
                   u.name = CURRENT_USER) OR {c} SUBSETEQ (  SELECT SUM(SET{au.class_of})  FROM _db_auth au  WHERE {au.grantee.name} SUBSETEQ
                   (  SELECT SET{CURRENT_USER} + COALESCE(SUM(SET{t.g.name}), SET{})  FROM db_user u, TABLE(groups) AS t(g)  WHERE u.name =
                   CURRENT_USER) AND  au.auth_type = 'SELECT')'

SHOW EXEC STATISTICS

The SHOW EXEC STATISTICS statement outputs statistics information of executing query.

  • To start collecting @collect_exec_stats statistics information, configure the value of session variable @collect_exec_stats to 1; to stop, configure it to 0.
  • It outputs the result of collecting statistics information.
  • The SHOW EXEC STATISTICS statement outputs four part of data page statistics information; data_page_fetches, data_page_dirties, data_page_ioreads, and data_page_iowrites. The result columns consist of variable column (name of statistics name) and value column (value of statistics value). Once the SHOW EXEC STATISTICS statement is executed, the statistics information which has been accumulated is initialized.
  • The SHOW EXEC STATISTICS ALL statement outputs all items of statistics information.

For details, see Dumping Statistics Information of Server.

SHOW EXEC STATISTICS [ALL]

The following example shows the result of executing query in the demodb database.

-- set session variable @collect_exec_stats as 1 to start collecting the statistical information.
SET @collect_exec_stats = 1;
SELECT * FROM db_class;
...

-- print the statistical information of the data pages.
SHOW EXEC STATISTICS;
variable value
============================================
'data_page_fetches' 332
'data_page_dirties' 85
'data_page_ioreads' 18
'data_page_iowrites' 28

SELECT * FROM db_index;
...

-- print all of the statistical information.
SHOW EXEC STATISTICS ALL;

variable value
============================================
'file_creates' 0
'file_removes' 0
'file_ioreads' 6
'file_iowrites' 0
'file_iosynches' 0
'data_page_fetches' 548
'data_page_dirties' 34
'data_page_ioreads' 6
'data_page_iowrites' 0
'data_page_victims' 0
'data_page_iowrites_for_replacement' 0
'log_page_ioreads' 0
'log_page_iowrites' 0
'log_append_records' 0
'log_checkpoints' 0
'log_wals' 0
'page_locks_acquired' 13
'object_locks_acquired' 9
'page_locks_converted' 0
'object_locks_converted' 0
'page_locks_re-requested' 0
'object_locks_re-requested' 8
'page_locks_waits' 0
'object_locks_waits' 0
'tran_commits' 3
'tran_rollbacks' 0
'tran_savepoints' 0
'tran_start_topops' 6
'tran_end_topops' 6
'tran_interrupts' 0
'btree_inserts' 0
'btree_deletes' 0
'btree_updates' 0
'btree_covered' 0
'btree_noncovered' 2
'btree_resumes' 0
'btree_multirange_optimization' 0
'query_selects' 4
'query_inserts' 0
'query_deletes' 0
'query_updates' 0
'query_sscans' 2
'query_iscans' 4
'query_lscans' 0
'query_setscans' 2
'query_methscans' 0
'query_nljoins' 2
'query_mjoins' 0
'query_objfetches' 0
'network_requests' 88
'adaptive_flush_pages' 0
'adaptive_flush_log_pages' 0
'adaptive_flush_max_pages' 0
'network_requests' 88
'adaptive_flush_pages' 0
'adaptive_flush_log_pages' 0
'adaptive_flush_max_pages' 0

Table Of Contents

Previous topic

PREPARED STATEMENT

Next topic

Query Optimization

This Page