MySQL SHOW COMMANDS

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 RELAYLOG EVENTS

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 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.

Let see all the character set starting with ‘utf” and Maxlen is 3:

https://googleads.g.doubleclick.net/pagead/ads?gdpr=0&us_privacy=1—&gpp_sid=-1&client=ca-pub-2153208817642134&output=html&h=280&adk=2413866252&adf=1810428289&pi=t.aa~a.4097345806~i.57~rp.4&w=715&fwrn=4&fwrnh=100&lmt=1706467649&num_ads=1&rafmt=1&armr=3&sem=mc&pwprc=5856759792&ad_type=text_image&format=715×280&url=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-show.php&fwr=0&pra=3&rh=179&rw=715&rpe=1&resp_fmts=3&wgl=1&fa=27&uach=WyJXaW5kb3dzIiwiMTAuMC4wIiwieDg2IiwiIiwiMTA2LjAuNDk5OC41MiIsbnVsbCwwLG51bGwsIjY0IixbWyJOb3RfQSBCcmFuZCIsIjguMC4wLjAiXSxbIkNocm9taXVtIiwiMTIwLjAuNjA5OS4yMzQiXSxbIk9wZXJhIiwiMTA2LjAuNDk5OC41MiJdXSwxXQ..&dt=1706467506716&bpp=13&bdt=4166&idt=14&shv=r20240122&mjsv=m202401240101&ptt=9&saldr=aa&abxe=1&cookie=ID%3D6de4a56fe4484587%3AT%3D1706354011%3ART%3D1706420688%3AS%3DALNI_MYWYkKy5gAvXyEH7W4ZN6WCTnP0sA&gpic=UID%3D00000d0223079700%3AT%3D1706354011%3ART%3D1706420688%3AS%3DALNI_MaBKEehqphuMfn0yJfqUi_NOyR70w&prev_fmts=468×80%2C304x250%2C300x600%2C300x600%2C0x0%2C715x280%2C1297x644&nras=4&correlator=4273584465680&frm=20&pv=1&ga_vid=1729879426.1706400821&ga_sid=1706467505&ga_hid=1814021088&ga_fc=1&u_tz=-480&u_his=18&u_h=768&u_w=1366&u_ah=728&u_aw=1366&u_cd=24&u_sd=1&dmc=8&adx=238&ady=577&biw=1312&bih=644&scr_x=0&scr_y=0&eid=44759876%2C44759927%2C44759837%2C44795922%2C31080663%2C95320377%2C95320888%2C95321627%2C95322163%2C95323009&oid=2&pvsid=825911308207069&tmod=925717838&uas=1&nvt=1&ref=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-security.php&fc=384&brdim=0%2C0%2C0%2C0%2C1366%2C0%2C1366%2C728%2C1312%2C644&vis=1&rsz=%7C%7Cs%7C&abl=NS&fu=128&bc=31&bz=1.04&psd=W251bGwsbnVsbCxudWxsLDNd&ifi=10&uci=a!a&fsb=1&dtd=Mmysql> 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.

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:

Sample 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:

Sample 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:

Sample 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:

Sample 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 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:

Sample 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 :

Sample 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;

Copy

and now, here is the statement-

SHOW ERRORS\G

Copy

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:

Sample 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;

Copy

Let execute the above and see the output:

Sample 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;

Copy

Let execute the above and see the output:

Sample 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

Copy

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 :

Sample Output:mysql> SHOW FUNCTION STATUS\G *************************** 1. row *************************** Db: empinfo Name: test1 Type: FUNCTION Definer: root@localhost 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:

Sample Output:mysql> SHOW FUNCTION STATUS like ‘tes%’\G *************************** 1. row *************************** Db: empinfo Name: test1 Type: FUNCTION Definer: root@localhost 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:

Sample Output:mysql> SHOW FUNCTION STATUS WHERE Db = ’empinfo’\G *************************** 1. row *************************** Db: empinfo Name: test1 Type: FUNCTION Definer: root@localhost 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';

Copy

Let execute the above and see the output:

Sample Output:+———————————————————————+ | Grants for root@localhost | +———————————————————————+ | 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 hostnamemysql> 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;

Copy

Let execute the above and see the output:

SHOW KEYS IN empinfo.employees;

Here are alternate statements for the above output

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

Copy

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

Copy

SHOW KEYS
IN employees 
WHERE column_name='employee_id';

Copy

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

Copy

Let execute the above and see the output:

Sample 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 :

https://www.adsensecustomsearchads.com/afs/ads?psid=5134551505&channel=AutoRsVariant&cx=r-440389826592af9d2&fexp=44759876%2C44759927%2C44759837%2C44795922%2C31080663%2C95320377%2C95320888%2C95321627%2C95322163%2C95323009%2C0%2C21404%2C17301383%2C71847096&client=pub-2153208817642134&r=m&sct=ID%3Df1c5d672aa31266c%3AT%3D1706354011%3ART%3D1706354011%3AS%3DALNI_MZlJMMg_q5l3r_1tPiuFzhttpHLOQ&sc_status=6&hl=en&rpbu=http%3A%2F%2Fgoogle.com&rpqp=q&type=3&rs_tt=c&oe=UTF-8&ie=UTF-8&format=r5&nocache=6721706467507153&num=0&output=afd_ads&domain_name=www.w3resource.com&v=3&bsl=10&pac=0&u_his=17&u_tz=-480&dt=1706467507157&u_w=1366&u_h=768&biw=1297&bih=644&psw=1297&psh=644&frm=0&cl=600476684&uio=-&cont=autors-container-0&drt=0&jsid=csa&jsv=600476684&rurl=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-show.php&referer=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-security.phpSHOW OPEN TABLES [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr]

See the following example.

SHOW OPEN TABLES FROM empinfo;

Copy

Let execute the above and see the output:

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%';

Copy

Let execute the above and see the output :

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

Copy

Let execute the above and see the output:

Sample 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

Copy

Let execute the above and see the output :

Sample 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

Copy

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:

Sample Output:mysql> SHOW PROCEDURE STATUS\G *************************** 1. row *************************** Db: empinfo Name: myprocedure Type: PROCEDURE Definer: root@localhost 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: root@localhost 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: root@localhost 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: root@localhost 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 :

Sample Output:mysql> SHOW PROCEDURE STATUS like ‘tes%’\G *************************** 1. row *************************** Db: empinfo Name: test Type: PROCEDURE Definer: root@localhost 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: root@localhost 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:

Sample Output:mysql> SHOW PROCEDURE STATUS WHERE Db = ’empinfo’\G *************************** 1. row *************************** Db: empinfo Name: myprocedure Type: PROCEDURE Definer: root@localhost 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: root@localhost 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: root@localhost 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: root@localhost 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

Copy

Let execute the above and see the output:

Sample 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;

Copy

Let execute the above and see the output:

Sample 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%';

Copy

Let execute the above and see the output:

Sample 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;

Copy

Let execute the above and see the output:

Sample Output:

Sample 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%';

Copy

Let execute the above and see the output:

Here is another example of SHOW TABLE STATUS using WHERE clause

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

Copy

Let execute the above and see the output:

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;

Copy

Let execute the above and see the output :

Here is alternate statements for the above output

SHOW TABLES FROM empinfo;

Copy

SHOW TABLES IN empinfo;

Copy

Here is the another example of SHOW TABLES with pattern matching

SHOW TABLES FROM empinfo LIKE 'e%';

Copy

Let execute the above and see the output:

Sample 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';

Copy

Let execute the above and see the output:

Sample 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;

Copy

Let execute the above and see the output :

Sample 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;

Copy

Let execute the above and see the output:

Sample 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%';

Copy

Let execute the above and see the output:

Here is another example of SHOW VARIABLES with LIKE

SHOW VARIABLES LIKE 'time%';

Copy

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 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;

Copy

and now, here is the statement-

SHOW WARNINGS\G

Copy

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:

Sample 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;

Copy

Let execute the above and see the output:

Sample 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;

Copy

Let execute the above and see the output:

Sample Output:mysql> SELECT @@warning_count; +—————–+ | @@warning_count | +—————–+ | 1 | +—————–+ 1 row in set (0.03 sec)

SHOW COMMANDS

Comments

Leave a Reply Cancel reply

More posts

Simple Blog System

User Login & Registration

To-Do List App

Image Upload System