MySQL SHOW

by Vincy. Last modified on July 2nd, 2022.

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.

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

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 a “root” user, then the above line will cause an error by showing an Access Denied message to the browser.

SHOW STATUS

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

SHOW VARIABLES

Like the MySQL SHOW STATUS command, it will also return a list of names, 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 the SHOW TABLES command, we need to select the database.

Otherwise, we can see the list of tables by specifying the database name with the SHOW TABLE command, like,

SHOW TABLES FROM <database-name>

SHOW INDEX and SHOW COLUMNS

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

For example, let there is a table named users in the 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, and settings and also to display the structure of database tables.

Vincy
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

Leave a Reply

Your email address will not be published. Required fields are marked *

↑ Back to Top

Share this page