MySQL SHOW

In Mysql, SHOW command is used to get the details of database users and their privileges. Using this command we can show database and its tables and also table column, index and etc.

But some of the SHOW command will return limited information based on the access privileges. In this article, we are going to see list of MySQL SHOW commands listed below.

  • SHOW GRANTS FOR
  • SHOW STATUS
  • SHOW VARIABLES
  • SHOW DATABASES
  • SHOW TABLES

mysql_show

SHOW GRANTS FOR

This command is used to display user privileges. For that, we need to specify our username and host to this command.

If we want to check with this command for root user in localhost, then the query is,

SHOW GRANTS FOR root@localhost

And, it returns user access privileges and related information as shown below.

show_grants_for

If we are not root user, then the above line will cause error by showing Access Denied message to the browser.

SHOW STATUS

This command returns large list of information represented by name, value pairs. These huge list of information is about the status report of database server regarding number of connection attempts, number of bytes received from and sent to the server and so on.

SHOW VARIABLES

Like MySQL SHOW STATUS command, it will also return list of name, value pairs about MySQL port, version and connection settings.

SHOW DATABASES and SHOW TABLES

SHOW DATABASES command will list all available databases. Like that, SHOW TABLES used to list tables. Before executing SHOW TABLES command, we need to select database. Otherwise, we can see the list of tables by specifying database name with SHOW TABLE command, like,

SHOW TABLES FROM <database-name>

SHOW INDEX and SHOW COLUMNS

As same as above for listing tables with given database name, we can show list of table index and columns by providing the table name and database name.

For example, let there is an users table in payload database. NOW these two commands can be used as shown below.

SHOW INDEX FROM users FROM payload

(and)

SHOW COLUMNS FROM users FROM payload

Thus, the SHOW command is used to get information about MySQL variables, settings and also to display the structure databases tables.

This MySQL code tutorial was published on May 16, 2013.

↑ Back to Top