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 commands will return limited information based on the access privileges. In this article, we are going to see the list of MySQL SHOW commands listed below.
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.
If we are not root user, then the above line will cause an error by showing Access Denied message to the browser.
This command returns a large list of information represented by name, value pairs. This huge list of information is the status report of database server regarding the number of connection attempts, a number of bytes received from and sent to the server and so on.
Like MySQL SHOW STATUS command, it will also return a list of name, value pairs about MySQL port, version and connection settings.
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>
As same as above for listing tables with given database name, we can show a list of table index and columns by providing the table name and database name.
For example, let there is a 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.