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.
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.
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.
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.