w3resource logo


MySQL Tutorial

MySQL SHOW COMMANDS

Secondary Nav

SHOW COMMANDS

There are various forms of MySQL SHOW commands, which provides information about databases, tables, columns, or status information about the commands. See the following section : :

Version : MySQL 5.6

Table of contents

MySQL SHOW BINARY LOGS MySQL SHOW ERRORS MySQL SHOW SLAVE HOSTS
MySQL SHOW BINLOG EVENTS MySQL SHOW EVENTS MySQL SHOW SLAVE STATUS
MySQL SHOW CHARACTER SET MySQL SHOW FUNCTION CODE MySQL SHOW STATUS
MySQL SHOW COLLATION MySQL SHOW FUNCTION STATUS MySQL SHOW TABLE STATUS
MySQL SHOW COLUMNS MySQL SHOW GRANTS MySQL SHOW TABLES
MySQL SHOW CREATE DATABASE MySQL SHOW INDEX MySQL SHOW TRIGGERS
MySQL SHOW CREATE EVENT MySQL SHOW MASTER STATUS MySQL SHOW VARIABLES
MySQL SHOW CREATE FUNCTION MySQL SHOW OPEN TABLES MySQL SHOW WARNINGS
MySQL SHOW CREATE PROCEDURE MySQL SHOW PLUGINS  
MySQL SHOW CREATE TABLE MySQL SHOW PRIVILEGES  
MySQL SHOW CREATE TRIGGER MySQL SHOW PROCEDURE CODE  
MySQL SHOW CREATE VIEW MySQL SHOW PROCEDURE STATUS  
MySQL SHOW DATABASE MySQL SHOW PROCESSLIST  
MySQL SHOW ENGINE MySQL SHOW RELAYLOG EVENTS  

 

MySQL : SHOW BINARY LOGS

SHOW BINARY LOGS statement is used to list the binary log files on the server. Here is the syntax :

SHOW BINARY LOGS             

See the following examples :

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000015 |    724935 |
| binlog.000016 |    733481 |
+---------------+-----------+

MySQL : SHOW BINLOG EVENTS

SHOW BINLOG EVENTS statement shows the events in the binary log. Here is the syntax :

SHOW BINLOG EVENTS
   [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]			     

If you omit 'log_name', the first binary log is displayed.

The LIMIT clause can be used to constrain the number of rows returned by the statement.

MySQL : SHOW CHARACTER SET

SHOW CHARACTER SET statement is used to check all available character sets. Here is the syntax :

SHOW CHARACTER SET     
     [LIKE 'pattern' | WHERE expr]
            

The optional LIKE clause, if present, shows the matched character set. With WHERE clause you can use a condition.

See the following examples :

mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
........
40 rows in set (0.03 sec)

Let see all the character set starting with 'utf"

mysql> SHOW CHARACTER SET LIKE 'utf%';
+---------+------------------+--------------------+--------+
| Charset | Description      | Default collation  | Maxlen |
+---------+------------------+--------------------+--------+
| utf8    | UTF-8 Unicode    | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode    | utf8mb4_general_ci |      4 |
| utf16   | UTF-16 Unicode   | utf16_general_ci   |      4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci |      4 |
| utf32   | UTF-32 Unicode   | utf32_general_ci   |      4 |
+---------+------------------+--------------------+--------+
5 rows in set (0.00 sec)

Let see all the character set starting with 'utf" and Maxlen is 3 :

mysql> SHOW CHARACTER SET  WHERE Maxlen=3;
+---------+---------------------------+---------------------+--------+
| Charset | Description               | Default collation   | Maxlen |
+---------+---------------------------+---------------------+--------+
| ujis    | EUC-JP Japanese           | ujis_japanese_ci    |      3 |
| utf8    | UTF-8 Unicode             | utf8_general_ci     |      3 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci |      3 |
+---------+---------------------------+---------------------+--------+
3 rows in set (0.00 sec)

MySQL : SHOW COLLATION

SHOW COLLATION statement is used to list collations (a collation is a set of rules for comparing characters in a character set) supported by the server. Here is the syntax :

SHOW COLLATION SET     
     [LIKE 'pattern' | WHERE expr]

The optional LIKE clause, if present, shows the matched collations.

With WHERE clause you can attach a condition.

See the following examples :

mysql> SHOW COLLATION;
+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
| dec8_bin                 | dec8     |  69 |         | Yes      |       1 |
| cp850_general_ci         | cp850    |   4 | Yes     | Yes      |       1 |
| cp850_bin                | cp850    |  80 |         | Yes      |       1 |
| hp8_english_ci           | hp8      |   6 | Yes     | Yes      |       1 |
| hp8_bin                  | hp8      |  72 |         | Yes      |       1 |
| koi8r_general_ci         | koi8r    |   7 | Yes     | Yes      |       1 |
| koi8r_bin                | koi8r    |  74 |         | Yes      |       1 |
| latin1_german1_ci        | latin1   |   5 |         | Yes      |       1 |
........
219 rows in set (0.06 sec)

Following command shows collation like utf :

mysql> SHOW COLLATION LIKE 'utf%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation                | Charset | Id  | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |
| utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 |
| utf8_latvian_ci          | utf8    | 194 |         | Yes      |       8 |
| utf8_romanian_ci         | utf8    | 195 |         | Yes      |       8 |
| utf8_slovenian_ci        | utf8    | 196 |         | Yes      |       8 |
| utf8_polish_ci           | utf8    | 197 |         | Yes      |       8 |
| utf8_estonian_ci         | utf8    | 198 |         | Yes      |       8 |
| utf8_spanish_ci          | utf8    | 199 |         | Yes      |       8 |
| utf8_swedish_ci          | utf8    | 200 |         | Yes      |       8 |
........
107 rows in set (0.00 sec)

MySQL : SHOW COLUMNS

The SHOW COLUMNS statement is used to display information about the columns in a given table. Here is the syntax :

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

The optional LIKE clause, if present shows the matched column names. With WHERE clause you can use a condition.

See the following examples :

mysql> SHOW COLUMNS FROM user_details;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| userid      | varchar(16)  | NO   | PRI | NULL    |       |
| password    | varchar(16)  | NO   |     | NULL    |       |
| fname       | varchar(100) | NO   |     | NULL    |       |
| lname       | varchar(100) | NO   |     | NULL    |       |
| gender      | varchar(1)   | NO   |     | NULL    |       |
| dtob        | date         | NO   |     | NULL    |       |
| country     | varchar(30)  | NO   |     | NULL    |       |
| user_rating | int(4)       | NO   |     | NULL    |       |
| emailid     | varchar(60)  | NO   |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
9 rows in set (0.25 sec)

MySQL : SHOW CREATE DATABASE

SHOW CREATE DATABASE statement is used to show CREATE DATABASE statement.

SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
See the following example :
mysql> SHOW CREATE DATABASE hr;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| hr       | CREATE DATABASE `hr` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL : SHOW CREATE EVENT

This statement displays the CREATE EVENT statement needed to re-create a given event. It requires the EVENT privilege for the database from which the event is to be shown.

SHOW CREATE EVENT event_name

MySQL : SHOW CREATE FUNCTION

SHOW CREATE FUNCTION statement is used to get the exact string that can be used to re-create the named stored function.

Here is the syntax :

SHOW CREATE FUNCTION func_name

Here is the statement to create a function 'test1'

mysql> CREATE FUNCTION test1 (aa CHAR(25))
    -> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('w3resource.',aa,' sites');
    -> //
Query OK, 0 rows affected (0.00 sec)

Here is the following statement of SHOW CREATE FUNCTION.

SHOW CREATE FUNCTION test1\G

Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:

Let execute the above and see the output :

mysql> SHOW CREATE FUNCTION test1\G
*************************** 1. row ***************************
            Function: test1
            sql_mode: 
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `test1`(aa CHAR(25)) RETURNS char(50) CHARSET latin1
    DETERMINISTIC
RETURN CONCAT('w3resource.',aa,' sites')
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

MySQL : SHOW CREATE PROCEDURE

SHOW CREATE PROCEDURE statement is used to get the exact string that can be used to re-create the named stored procedure. The statement requires that you must be the owner of the routine.

Here is the syntax :

SHOW CREATE PROCEDURE proc_name

Here is the statement to create a procedure 'myprocedure'

mysql> CREATE PROCEDURE myprocedure (OUT emp_ctr INT)
    -> BEGIN 
    -> SELECT COUNT(*) INTO emp_ctr FROM empinfo.employees; 
    -> END//
Query OK, 0 rows affected (0.00 sec)

Here is the following statement of SHOW CREATE PROCEDURE.

SHOW CREATE PROCEDURE empinfo.myprocedure\G

Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:

Let execute the above and see the output :

mysql> SHOW CREATE PROCEDURE empinfo.myprocedure\G
*************************** 1. row ***************************
           Procedure: myprocedure
            sql_mode: 
    Create Procedure: CREATE DEFINER=`root`@`localhost` 
PROCEDURE `myprocedure`(OUT emp_ctr INT)
BEGIN 
SELECT COUNT(*) INTO emp_ctr FROM empinfo.employees; 
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

MySQL : SHOW CREATE TABLE

SHOW CREATE TABLE statement is used to show the create table statement.

Here is the syntax :

SHOW CREATE TABLE  table_name;

See the following example:

SHOW CREATE TABLE  regions\G;

Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:

Let execute the above and see the output :

mysql> SHOW CREATE TABLE  regions\G
*************************** 1. row ***************************
       Table: regions
Create Table: CREATE TABLE `regions` (
  `REGION_ID` decimal(5,0) NOT NULL,
  `REGION_NAME` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`REGION_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MySQL : SHOW CREATE TRIGGER

SHOW CREATE TRIGGERS statement is used to show the CREATE TRIGGER statement that creates the named trigger.

Here is the syntax :

SHOW CREATE TRIGGER trigger_name

See the following example:

mysql> SHOW CREATE TRIGGER ins_sum\G;
*************************** 1. row ***************************
               Trigger: ins_sum
              sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`::1` TRIGGER ins_sum BEFORE INSERT ON account 
FOR EACH ROW SET @sum = @sum + NEW.amount
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: utf8_general_ci
1 row in set (0.02 sec)
ERROR: 
No query specified

MySQL : SHOW CREATE VIEW

SHOW CREATE VIEW statement is used to show the create view statement.

Here is the syntax :

SHOW CREATE VIEW  view_name;

See the following example:

SHOW CREATE VIEW  myview\G;

Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:

Let execute the above and see the output :

mysql> SHOW CREATE VIEW MYVIEW\G;
*************************** 1. row ***************************
                View: myview
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 
		 SQL SECURITY DEFINER VIEW `myview` AS 
		 select `departments`.`DEPARTMENT_ID` AS `DEPARTMENT_ID`,
		 `departments`.`DEPARTMENT_NAME` AS `DEPARTMENT_NAME`,
		 `departments`.`MANAGER_ID` AS `MANAGER_ID`,
		 `departments`.`LOCATION_ID` AS `LOCATION_ID` 
		 from `departments`
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.13 sec)

MySQL : SHOW DATABASES

SHOW DATABASES statement is used to lists the databases on the MySQL server host. The SHOW SCHEMAS can be used as a synonym for SHOW DATABASES.

Here is the syntax :

SHOW {DATABASES | SCHEMAS}
    [LIKE 'pattern' | WHERE expr]

If the LIKE clause is present along with the SHOW DATABASES, indicates which database names to match.

See the following example:

SHOW DATABASES;

Let execute the above and see the output :

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| bookinfo           | 
| bupayroll          | 
| bupf               | 
| empinfo          | 
| mucpf              | 
| mucstuinfo         | 
| mysql              | 
+--------------------+
8 rows in set (0.16 sec)

Here is the alternate statement of SHOW DATABASES :

mysql> SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| bookinfo           | 
| bupayroll          | 
| bupf               | 
| empinfo          | 
| mucpf              | 
| mucstuinfo         | 
| mysql              | 
+--------------------+
8 rows in set (0.02 sec)

Here is the example of SHOW DATABASES using LIKE.

mysql> SHOW DATABASES LIKE 'm%';
+---------------+
| Database (m%) |
+---------------+
| mucpf         | 
| mucstuinfo    | 
| mysql         | 
+---------------+
3 rows in set (0.03 sec)

MySQL : SHOW ENGINE

The SHOW ENGINE statement is used to display operational information about a storage engine.

Here is the syntax :

SHOW ENGINE INNODB STATUS
SHOW ENGINE INNODB MUTEX
SHOW ENGINE {NDB | NDBCLUSTER} STATUS
SHOW ENGINE PERFORMANCE_SCHEMA STATUS

See the following example:

SHOW ENGINE INNODB STATUS\G;

Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:

Let execute the above and see the output :

mysql> SHOW ENGINE INNODB STATUS\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
130729 18:26:13 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 11 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3, signal count 3
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 4, OS waits 2; RW-excl spins 1, OS waits 1
------------
TRANSACTIONS
------------
Trx id counter 0 1792
Purge done for trx's n:o < 0 0 undo n:o < 0 0
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 3512
MySQL thread id 1, query id 16 localhost 127.0.0.1 root
SHOW ENGINE INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
29 OS file reads, 3 OS file writes, 3 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 46419
Log flushed up to   0 46419
Last checkpoint at  0 46419
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 14857048; in additional pool allocated 857856
Dictionary memory allocated 20024
Buffer pool size   512
Free buffers       493
Database pages     19
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 19, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 1032, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)

MySQL : SHOW ENGINES

The SHOW ENGINES statement is used to display the status information about the server's storage engines. It is important for checking whether a storage engine is supported, or what the default engine is.

Here is the syntax :

SHOW [STORAGE] ENGINES;

See the following example:

SHOW ENGINES\G

Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:

Let execute the above and see the output :

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 3. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: YES
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
8 rows in set (0.00 sec)

MySQL : SHOW ERRORS

The SHOW ERRORS statement is used to display the errors, warnings, and notes. This statement is almost similar to SHOW WARNINGS except displaying errors.

Here is the syntax :

SHOW ERRORS [LIMIT [offset,] row_count]
SHOW COUNT(*) ERRORS

The LIMIT clause can be used to specify the number of rows to be retrieved.

The offset is an argument, which LIMIT takes optionally to retrieve the number of rows. When mention two arguments, the first one is from a particular position and the second one is a number of rows after the first one position.

The offset of the initial row is 0 (not 1)

The SHOW COUNT(*) ERRORS statement is used to displays the number of errors.

See the following example:

Here, in the below statement, there is an error. Execute this statement an error message will be generated.

SHOW DATABASE;

and now, here is the statement-

SHOW ERRORS\G

Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:

Let execute the above and see the output :

mysql> SHOW ERRORS\G
*************************** 1. row ***************************
  Level: Error
   Code: 1064
Message: You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'database' at line 1
1 row in set (0.02 sec)
SHOW COUNT(*) ERRORS;

Let execute the above and see the output :

mysql> SHOW COUNT(*) ERRORS;
+-----------------------+
| @@session.error_count |
+-----------------------+
|                     1 | 
+-----------------------+
1 row in set (0.11 sec)

Number of errors can also be retrieve using the error_count variable in a SELECT statement.

SELECT @@error_count;

Let execute the above and see the output :

mysql> SELECT @@error_count;
+---------------+
| @@error_count |
+---------------+
|             1 | 
+---------------+
1 row in set (0.06 sec)

MySQL : SHOW EVENTS

The SHOW EVENTS statement is used to display information about Event Manager events. It requires the EVENT privilege for the database from which the events are to be shown.

Here is the syntax :

SHOW EVENTS [{FROM | IN} schema_name]
    [LIKE 'pattern' | WHERE expr]

MySQL : SHOW FUNCTION CODE

This statement is similar to SHOW PROCEDURE CODE but for stored functions.

MySQL : SHOW FUNCTION STATUS

This SHOW FUNCTION STATUS statement returns the characteristics of a stored function, such as the database, name, type, creator, creation and modification dates, and character set information.

Here is the syntax :

SHOW FUNCTION STATUS
    [LIKE 'pattern' | WHERE expr]

See the following example.

SHOW FUNCTION STATUS\G

Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:

Let execute the above and see the output :

mysql> SHOW FUNCTION STATUS\G
*************************** 1. row ***************************
                  Db: empinfo
                Name: test1
                Type: FUNCTION
             Definer: [email protected]
            Modified: 2013-07-31 17:03:05
             Created: 2013-07-31 17:03:05
       Security_type: DEFINER
             Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

Let execute the example below using pattern matching and see the output :

mysql> SHOW FUNCTION STATUS like 'tes%'\G
*************************** 1. row ***************************
                  Db: empinfo
                Name: test1
                Type: FUNCTION
             Definer: [email protected]
            Modified: 2013-07-31 17:03:05
             Created: 2013-07-31 17:03:05
       Security_type: DEFINER
             Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

Let execute the example below using where and see the output :

mysql> SHOW FUNCTION STATUS WHERE Db = 'empinfo'\G
*************************** 1. row ***************************
                 Db: empinfo
                Name: test1
                Type: FUNCTION
             Definer: [email protected]
            Modified: 2013-07-31 17:03:05
             Created: 2013-07-31 17:03:05
       Security_type: DEFINER
             Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

MySQL : SHOW GRANTS

The SHOW GRANTS statement is used to list the GRANT statement or statements that must be issued to duplicate the privileges that are granted to a MySQL user account. The account is named using the same format as for the GRANT statement; If you specify only the username part of the account name, a host name part of '%' is used.

Here is the syntax :

SHOW GRANTS [FOR user]

See the following example.

SHOW GRANTS FOR 'root'@'localhost';

Let execute the above and see the output :

+---------------------------------------------------------------------+
| Grants for [email protected]                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | 
+---------------------------------------------------------------------+
1 row in set (0.03 sec)

Here is the another example of not using the hostname

mysql> SHOW GRANTS FOR 'root';
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION | 
+-------------------------------------------------------------+
1 row in set (0.00 sec)

In the above example, only the username have specified as the part of the account name that is why in host name part a '%' have appeared.

MySQL : SHOW INDEX

The SHOW INDEX statement returns the information of index of a table.

Here is the syntax :

SHOW {INDEX | INDEXES | KEYS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[WHERE expr]

See the following example.

SHOW INDEX FROM employees;

Let execute the above and see the output :

mysql> SHOW INDEX FROM employees;
+-----------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| employees |          0 | PRIMARY           |            1 | EMPLOYEE_ID   | A         |         107 |     NULL | NULL   |      | BTREE      |         | 
| employees |          0 | EMP_EMAIL_UK      |            1 | EMAIL         | A         |         107 |     NULL | NULL   |      | BTREE      |         | 
| employees |          1 | EMP_DEPARTMENT_IX |            1 | DEPARTMENT_ID | A         |          11 |     NULL | NULL   | YES  | BTREE      |         | 
| employees |          1 | EMP_JOB_IX        |            1 | JOB_ID        | A         |          17 |     NULL | NULL   |      | BTREE      |         | 
| employees |          1 | EMP_MANAGER_IX    |            1 | MANAGER_ID    | A         |          17 |     NULL | NULL   | YES  | BTREE      |         | 
| employees |          1 | EMP_NAME_IX       |            1 | LAST_NAME     | A         |         107 |     NULL | NULL   |      | BTREE      |         | 
| employees |          1 | EMP_NAME_IX       |            2 | FIRST_NAME    | A         |         107 |     NULL | NULL   | YES  | BTREE      |         | 
+-----------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.00 sec)

SHOW KEYS IN empinfo.employees;

Here is another example of SHOW INDEX statement using where clause

mysql> SHOW INDEX FROM employees 
    -> FROM empinfo WHERE column_name='employee_id';
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| employees |          0 | PRIMARY  |            1 | EMPLOYEE_ID | A         |         107 |     NULL | NULL   |      | BTREE      |         | 
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

Here are alternate statements for the above output

SHOW INDEX 
FROM empinfo.employees 
WHERE column_name='employee_id';
SHOW KEYS
FROM empinfo.employees 
WHERE column_name='employee_id';
SHOW KEYS
IN employees 
WHERE column_name='employee_id';

MySQL : SHOW MASTER STATUS

The SHOW MASTER STATUS statement provides status information about the binary log files of the master. It requires either the SUPER or REPLICATION CLIENT privilege.

Here is the syntax :

SHOW MASTER STATUS

See the following example.

SHOW MASTER STATUS\G

Let execute the above and see the output :

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mysql-bin.000129
        Position: 106
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.03 sec)

Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:

MySQL : SHOW OPEN TABLES

SHOW OPEN TABLES statement is used to list the non-TEMPORARY tables that are currently open in the table cache. Here is the syntax :

SHOW OPEN TABLES [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

See the following example.

SHOW OPEN TABLES FROM empinfo;

Let execute the above and see the output :

mysql> SHOW OPEN TABLES FROM empinfo;
+-----------+-------------+--------+-------------+
| Database  | Table       | In_use | Name_locked |
+-----------+-------------+--------+-------------+
| empinfo | employees   |      0 |           0 | 
| empinfo | departments |      0 |           0 | 
+-----------+-------------+--------+-------------+
2 rows in set (0.02 sec)

The SHOW OPEN TABLES output has the following columns:

Database - the name of the database currently being used.

Table - name of the non temporary table(s) currently opened.

In_use - The number of table locks or lock requests there is in the database for the table.

Name_locked - Whether the table name is locked. Name locking is used for the operations like dropping or renaming tables.

If there are no privileges for a table, it does not show up in the output from SHOW OPEN TABLES.

Here is the another example of pattern matching

SHOW OPEN TABLES FROM employee LIKE 'e%';

Let execute the above and see the output :

mysql> SHOW OPEN TABLES FROM employee LIKE 'e%';
+-----------+-----------+--------+-------------+
| Database  | Table     | In_use | Name_locked |
+-----------+-----------+--------+-------------+
| employee | employees |      0 |           0 | 
+-----------+-----------+--------+-------------+
1 row in set (0.00 sec)

MySQL : SHOW PLUGINS

The SHOW PLUGINS statement is used to display the information about server plugins. The information or Plugins is also available in the INFORMATION_SCHEMA.PLUGINS table.

Here is the syntax :

SHOW PLUGINS

See the following example.

SHOW PLUGINS\G

Let execute the above and see the output :

mysql> SHOW PLUGINS\G
*************************** 1. row ***************************
   Name: binlog
 Status: ACTIVE
   Type: STORAGE ENGINE
Library: NULL
License: GPL
*************************** 2. row ***************************
   Name: MEMORY
 Status: ACTIVE
   Type: STORAGE ENGINE
Library: NULL
License: GPL
*************************** 3. row ***************************
   Name: MyISAM
 Status: ACTIVE
   Type: STORAGE ENGINE
Library: NULL
License: GPL
*************************** 4. row ***************************
   Name: MRG_MYISAM
 Status: ACTIVE
   Type: STORAGE ENGINE
Library: NULL
License: GPL
....

Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:

MySQL : SHOW PRIVILEGES

The SHOW PRIVILEGES statement shows the list of system privileges that the MySQL server supports. The exact list of privileges depends on the version of the server which you are using.

Here is the syntax :

SHOW PRIVILEGES

See the following example.

SHOW PRIVILEGES\G

Let execute the above and see the output :

mysql> SHOW PRIVILEGES\G
*************************** 1. row ***************************
Privilege: Alter
  Context: Tables
  Comment: To alter the table
*************************** 2. row ***************************
Privilege: Alter routine
  Context: Functions,Procedures
  Comment: To alter or drop stored functions/procedures
*************************** 3. row ***************************
Privilege: Create
  Context: Databases,Tables,Indexes
  Comment: To create new databases and tables
*************************** 4. row ***************************
Privilege: Create routine
  Context: Databases
  Comment: To use CREATE FUNCTION/PROCEDURE
*************************** 5. row ***************************
Privilege: Create temporary tables
  Context: Databases
  Comment: To use CREATE TEMPORARY TABLE
*************************** 6. row ***************************
Privilege: Create view
  Context: Tables
  Comment: To create new views
....

Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:

MySQL : SHOW PROCEDURE CODE

The SHOW PROCEDURE CODE statement is used to display the internal implementation of the named stored procedure. This statement is a is available only for servers that have been built with debugging support.

Here is the syntax :

SHOW PROCEDURE CODE proc_name

See the following example, here the server has not built with debugging support.

mysql> SHOW PROCEDURE CODE job_data;
ERROR 1289 (HY000): The 'SHOW PROCEDURE|FUNCTION CODE' feature is disabled; you need MySQL built with '--with-debug' to have it working

MySQL : SHOW PROCEDURE STATUS

This SHOW PROCEDURE STATUS statement returns the characteristics of a stored procedure, such as the database, name, type, creator, creation and modification dates, and character set information.

Here is the syntax :

SHOW PROCEDURE STATUS
    [LIKE 'pattern' | WHERE expr]

See the following example.

SHOW PROCEDURE STATUS\G

Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:

Let execute the above and see the output :

mysql> SHOW PROCEDURE STATUS\G
*************************** 1. row ***************************
                  Db: empinfo
                Name: myprocedure
                Type: PROCEDURE
             Definer: [email protected]
            Modified: 2013-07-30 16:17:14
             Created: 2013-07-30 16:17:14
       Security_type: DEFINER
             Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
                  Db: empinfo
                Name: proc1
                Type: PROCEDURE
             Definer: [email protected]
            Modified: 2013-07-31 10:44:07
             Created: 2013-07-31 10:44:07
       Security_type: DEFINER
             Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
*************************** 3. row ***************************
                  Db: empinfo
                Name: test
                Type: PROCEDURE
             Definer: [email protected]
            Modified: 2013-06-20 17:28:09
             Created: 2013-06-20 17:28:09
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 4. row ***************************
                  Db: empinfo
                Name: test1
                Type: PROCEDURE
             Definer: [email protected]
            Modified: 2013-07-30 17:48:16
             Created: 2013-07-30 17:48:16
       Security_type: DEFINER
             Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
4 rows in set (0.22 sec)

Let execute the example below using pattern matching and see the output :

mysql> SHOW PROCEDURE STATUS like 'tes%'\G
*************************** 1. row ***************************
                  Db: empinfo
                Name: test
                Type: PROCEDURE
             Definer: [email protected]
            Modified: 2013-06-20 17:28:09
             Created: 2013-06-20 17:28:09
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
                  Db: empinfo
                Name: test1
                Type: PROCEDURE
             Definer: [email protected]
            Modified: 2013-07-30 17:48:16
             Created: 2013-07-30 17:48:16
       Security_type: DEFINER
             Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
2 rows in set (0.00 sec)

Let execute the example below using where and see the output :

mysql> SHOW PROCEDURE STATUS WHERE Db = 'empinfo'\G
*************************** 1. row ***************************
                  Db: empinfo
                Name: myprocedure
                Type: PROCEDURE
             Definer: [email protected]
            Modified: 2013-07-30 16:17:14
             Created: 2013-07-30 16:17:14
       Security_type: DEFINER
             Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
                  Db: empinfo
                Name: proc1
                Type: PROCEDURE
             Definer: [email protected]
            Modified: 2013-07-31 10:44:07
             Created: 2013-07-31 10:44:07
       Security_type: DEFINER
             Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
*************************** 3. row ***************************
                  Db: empinfo
                Name: test
                Type: PROCEDURE
             Definer: [email protected]
            Modified: 2013-06-20 17:28:09
             Created: 2013-06-20 17:28:09
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 4. row ***************************
                  Db: empinfo
                Name: test1
                Type: PROCEDURE
             Definer: [email protected]
            Modified: 2013-07-30 17:48:16
             Created: 2013-07-30 17:48:16
       Security_type: DEFINER
             Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
4 rows in set (0.02 sec)

MySQL : SHOW PROCESSLIST

The SHOW PROCESSLIST statement shows you which threads are running. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.

Here is the syntax :

SHOW [FULL] PROCESSLIST

See the following example.

SHOW  PROCESSLIST\G

Let execute the above and see the output :

mysql> SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost:1300
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: SHOW FULL PROCESSLIST
1 row in set (0.00 sec)

Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout.

MySQL : SHOW RELAYLOG EVENTS

The SHOW RELAYLOG statement shows the events in the relay log of a replication slave. If you do not specify 'log_name', the first relay log is displayed.

Here is the syntax :

SHOW RELAYLOG EVENTS
   [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

If 'log_name', is not specified the first relay log is displayed. This statement has no effect on the master.

MySQL : SHOW SLAVE HOSTS

SHOW SLAVE HOSTS statement is used to display a list of replication slaves currently registered with the master.

Here is the syntax :

SHOW SLAVE HOSTS

MySQL : SHOW SLAVE STATUS

The SHOW SLAVE STATUS statement provides status information on essential parameters of the slave threads.

Here is the syntax :

SHOW SLAVE STATUS

The statement requires either the SUPER or REPLICATION CLIENT privilege.

MySQL : SHOW STATUS

The SHOW STATUS statement provides the information of server status. The LIKE clause along with this statement helps to match the specific variable. The usage of WHERE clause can fetch rows against general conditions. This statement does not require any privilege.

Here is the syntax :

SHOW [GLOBAL | SESSION] STATUS
    [LIKE 'pattern' | WHERE expr]

See the following example.

SHOW  STATUS;

Let execute the above and see the output :

mysql> SHOW STATUS;
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Aborted_clients                   | 0        | 
| Aborted_connects                  | 0        | 
| Binlog_cache_disk_use             | 0        | 
| Binlog_cache_use                  | 0        | 
| Bytes_received                    | 263      | 
| Bytes_sent                        | 2006     | 
| Com_admin_commands                | 0        | 
...
| Compression                       | OFF      | 
| Connections                       | 15       | 
| Created_tmp_disk_tables           | 0        | 
| Created_tmp_files                 | 5        | 
| Created_tmp_tables                | 0        | 
| Delayed_errors                    | 0        | 
...
| Flush_commands                    | 1        | 
| Handler_commit                    | 0        | 
| Handler_delete                    | 0        | 
| Handler_discover                  | 0        | 
...
| Innodb_buffer_pool_pages_data     | 19       | 
| Innodb_buffer_pool_pages_dirty    | 0        | 
| Innodb_buffer_pool_pages_flushed  | 0        | 
| Innodb_buffer_pool_pages_free     | 493      | 
...
| Key_blocks_not_flushed            | 0        | 
| Key_blocks_unused                 | 14347    | 
...
| Tc_log_max_pages_used             | 0        | 
| Tc_log_page_size                  | 0        | 
| Tc_log_page_waits                 | 0        | 
| Threads_cached                    | 0        | 
| Threads_connected                 | 1        | 
| Threads_created                   | 14       | 
| Threads_running                   | 1        | 
| Uptime                            | 2029     | 
| Uptime_since_flush_status         | 2029     | 
+-----------------------------------+----------+
291 rows in set (0.20 sec)

Here is the another example.

SHOW STATUS LIKE 'Qca%';

Let execute the above and see the output :

mysql> SHOW STATUS LIKE 'Qca%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     | 
| Qcache_free_memory      | 0     | 
| Qcache_hits             | 0     | 
| Qcache_inserts          | 0     | 
| Qcache_lowmem_prunes    | 0     | 
| Qcache_not_cached       | 0     | 
| Qcache_queries_in_cache | 0     | 
| Qcache_total_blocks     | 0     | 
+-------------------------+-------+
8 rows in set (0.00 sec)

MySQL : SHOW TABLE STATUS

The SHOW TABLE STATUS statement provides a lot of information about each non-TEMPORARY table. The LIKE clause, if present, indicates which table names to match. The usage of WHERE clause can fetch rows against general conditions.

Here is the syntax :

SHOW TABLE STATUS [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

See the following example.

SHOW TABLE STATUS;

Let execute the above and see the output :

mysql> SHOW TABLE STATUS;
+-------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| countries   | MyISAM |      10 | Dynamic    |   25 |             23 |         580 |  281474976710655 |         3072 |         0 |           NULL | 2013-03-09 17:52:17 | 2013-03-13 18:39:26 | 2013-03-13 18:39:22 | latin1_swedish_ci |     NULL |                |         | 
| departments | MyISAM |      10 | Dynamic    |   27 |             24 |         656 |  281474976710655 |         4096 |         0 |           NULL | 2013-03-09 18:12:13 | 2013-03-13 17:54:15 | 2013-03-13 17:50:03 | latin1_swedish_ci |     NULL |                |         | 
| employees   | MyISAM |      10 | Dynamic    |  107 |             66 |        7164 |  281474976710655 |        14336 |         0 |           NULL | 2013-03-09 18:05:26 | 2013-03-13 18:36:09 | 2013-03-13 18:36:07 | latin1_swedish_ci |     NULL |                |         | 
| job_history | MyISAM |      10 | Dynamic    |   11 |             25 |         276 |  281474976710655 |         5120 |         0 |           NULL | 2013-03-09 18:16:48 | 2013-03-13 18:31:20 | 2013-03-13 18:31:16 | latin1_swedish_ci |     NULL |                |         | 
| jobs        | MyISAM |      10 | Dynamic    |   19 |             37 |         720 |  281474976710655 |         2048 |         0 |           NULL | 2013-03-09 18:13:33 | 2013-03-13 17:50:41 | NULL                | latin1_swedish_ci |     NULL |                |         | 
| locations   | MyISAM |      10 | Dynamic    |   23 |             53 |        1220 |  281474976710655 |         5120 |         0 |           NULL | 2013-03-09 17:58:56 | 2013-03-13 17:54:15 | 2013-03-13 17:46:40 | latin1_swedish_ci |     NULL |                |         | 
....

Here is another example of SHOW TABLE STATUS using pattern matching

SHOW TABLE STATUS FROM employee LIKE 'job%';

Let execute the above and see the output :

mysql> SHOW TABLE STATUS FROM employee LIKE 'job%';
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| job_history | MyISAM |      10 | Dynamic    |   11 |             25 |         276 | 281474976710655 |         5120 |         0 |           NULL | 2013-03-09 18:16:48 | 2013-03-13 18:31:20 | 2013-03-13 18:31:16 | latin1_swedish_ci |     NULL |                |         | 
| jobs        | MyISAM |      10 | Dynamic    |   19 |             37 |         720 | 281474976710655 |         2048 |         0 |           NULL | 2013-03-09 18:13:33 | 2013-03-13 17:50:41 | NULL                | latin1_swedish_ci |     NULL |                |         | 
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
2 rows in set (0.00 sec)

Here is another example of SHOW TABLE STATUS using WHERE clause

SHOW TABLE STATUS FROM employee WHERE name='countries';

Let execute the above and see the output :

mysql> SHOW TABLE STATUS FROM employee WHERE name='countries';
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| countries | MyISAM |      10 | Dynamic    |   25 |             23 |         580 | 281474976710655 |         3072 |         0 |           NULL | 2013-03-09 17:52:17 | 2013-03-13 18:39:26 | 2013-03-13 18:39:22 | latin1_swedish_ci |     NULL |                |         | 
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

MySQL : SHOW TABLES

SHOW TABLES lists the non-TEMPORARY tables in a given database. The LIKE clause, if present, indicates which table names to match. The usage of WHERE clause can fetch rows against general conditions.

Here is the syntax :

SHOW [FULL] TABLES [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

See the following example.

SHOW  TABLES;

Let execute the above and see the output :

mysql> SHOW TABLES;
+---------------------+
| Tables_in_empinfo |
+---------------------+
| countries           | 
| departments         | 
| employees           | 
| job_history         | 
| jobs                | 
| locations           | 
| myview              | 
| regions             | 
| table1              | 
| table11             | 
| table111            | 
| table112            | 
| table113            | 
| table114            | 
| table12             | 
| table13             | 
| table2              | 
| test1               | 
| test2               | 
+---------------------+
19 rows in set (0.39 sec)

Here is alternate statements for the above output

SHOW TABLES FROM empinfo;
SHOW TABLES IN empinfo;

Here is the another example of SHOW TABLES with pattern matching

SHOW TABLES FROM empinfo LIKE 'e%';

Let execute the above and see the output :

mysql> SHOW TABLES FROM empinfo LIKE 'e%';
+--------------------------+
| Tables_in_empinfo (e%) |
+--------------------------+
| employees                | 
+--------------------------+
1 row in set (0.02 sec)

Here is the another example of SHOW TABLES with WHERE clause

SHOW TABLES FROM empinfo 
WHERE Tables_in_empinfo='employees';

Let execute the above and see the output :

mysql> SHOW TABLES FROM empinfo 
WHERE Tables_in_empinfo='employees';
+---------------------+
| Tables_in_empinfo |
+---------------------+
| employees           | 
+---------------------+
1 row in set (0.00 sec)

MySQL : SHOW TRIGGERS

SHOW TRIGGERS statement is used to list the triggers currently defined for tables in a database

Here is the syntax :

SHOW TRIGGERS [{FROM | IN} db_name]     
[LIKE 'pattern' | WHERE expr]

MySQL : SHOW VARIABLES

The SHOW VARIABLES statement shows the values of MySQL system variables. The LIKE clause, if present, indicates which table names to match. The usage of WHERE clause can fetch rows against general conditions. This statement does not require any privilege. It requires only the ability to connect to the server.

Here is the syntax :

SHOW [GLOBAL | SESSION] VARIABLES
    [LIKE 'pattern' | WHERE expr]

See the following example.

SHOW  VARIABLES;

Let execute the above and see the output :

mysql> SHOW VARIABLES;
+-----------------------------------------+---------------------------------------------+
| Variable_name                           | Value                                       |
+-----------------------------------------+---------------------------------------------+
| auto_increment_increment                | 1                                           | 
| auto_increment_offset                   | 1                                           | 
| autocommit                              | ON                                          | 
| automatic_sp_privileges                 | ON                                          | 
| back_log                                | 50                                          | 
| basedir                                 | c:\wamp\bin\mysql\mysql5.1.36\              | 
| big_tables                              | OFF                                         | 
| binlog_cache_size                       | 32768                                       | 
...

Here is another example of SHOW VARIABLES with GLOBAL

SHOW GLOBAL VARIABLES;

Let execute the above and see the output :

mysql> SHOW GLOBAL VARIABLES;
+-----------------------------------------+----------------------------------------------+
| Variable_name                           | Value                                        |
+-----------------------------------------+----------------------------------------------+
| auto_increment_increment                | 1                                            | 
| auto_increment_offset                   | 1                                            | 
| autocommit                              | ON                                           | 
| automatic_sp_privileges                 | ON                                           | 
| back_log                                | 50                                           | 
| basedir                                 | c:\wamp\bin\mysql\mysql5.1.36\               | 
| big_tables                              | OFF                                          | 
...

Here is another example of SHOW VARIABLES with LIKE

SHOW VARIABLES LIKE 'time%';

Let execute the above and see the output :

mysql> SHOW VARIABLES LIKE 'time%';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| time_format   | %H:%i:%s   | 
| time_zone     | SYSTEM     | 
| timed_mutexes | OFF        | 
| timestamp     | 1375447849 | 
+---------------+------------+
4 rows in set (0.00 sec)

Here is another example of SHOW VARIABLES with LIKE

SHOW VARIABLES LIKE 'time%';

MySQL : SHOW WARNINGS

The SHOW WARNINGS statement is used to display the warnings,errors, and notes that resulted from the last statement in the current session that generated messages. It shows nothing if the last statement does not generate any message.

Here is the syntax :

SHOW WARNINGS [LIMIT [offset,] row_count]
SHOW COUNT(*) WARNINGS

The LIMIT clause can be used to specify the number of rows to be retrieved.

The offset is an argument, which LIMIT takes optionally to retrieve the number of rows. When mention two arguments, the first one is from a particular position and the second one is a number of rows after the first one position.

The offset of the initial row is 0 (not 1)

The SHOW COUNT(*) WARNINGS statement is used to displays the number of warnings.

See the following example:

Here, in the below statement, there is an error. Execute this statement an error message will be generated.

SELECT * FORM employees;

and now, here is the statement-

SHOW WARNINGS\G

Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:

Let execute the above and see the output :

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Error
   Code: 1064
Message: You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 
'FORM EMPLOYEES' at line 1
1 row in set (0.00 sec)
SHOW COUNT(*) WARNINGS;

Let execute the above and see the output :

mysql> SHOW COUNT(*) WARNINGS;
+-------------------------+
| @@session.warning_count |
+-------------------------+
|                       1 | 
+-------------------------+
1 row in set (0.13 sec)

A number of warnings can also be retrieve using the warning_count variable in a SELECT statement.

SELECT @@warning_count;

Let execute the above and see the output :

mysql> SELECT @@warning_count;
+-----------------+
| @@warning_count |
+-----------------+
|               1 | 
+-----------------+
1 row in set (0.03 sec)

Reference: MySQL 5.6 Manual



Join our Question Answer community to learn and share your programming knowledge.