Back to Top

How to use MySQL SHOW Command

MySQL SHOW Commands

MySQL has a lot of basic commands and show commands is one of them.MySQL has SHOW command which have a several different uses.MySQL SHOW Command generates output displaying a useful information about your Schema.

What is SHOW Command in MySQL

MySQL database, users, and tables.SHOW Command depends on the access level at database so depending on the access level, SHOW commands will not be available to provide only information.The root-level user has the capability to use all the SHOW commands, with the most comprehensive results.

Let’s have a look into the common uses of SHOW commands and learn about in more detail:

  1. SHOW GRANTS FOR user
  2. SHOW DATABASES [LIKE value]
  3. SHOW [OPEN] TABLES [FROM database_name] [LIKE value]
  4. SHOW CREATE TABLE table_name
  5. SHOW [FULL] COLUMNS FROM table_name [FROM database_name] [LIKE value]
  6. SHOW INDEX FROM table_name [FROM database_name]
  7. SHOW TABLE STATUS [FROM db_name] [LIKE value]
  8. SHOW STATUS [LIKE value]
  9. SHOW VARIABLES [LIKE value]

1. SHOW GRANTS FOR user

The SHOW GRANTS command displays the privileges for a given user at a given host.It’s a easiest way to get user’s privileges information and to check on the current status of a user that user have access of modification or not.you can check that the user doesn’t already have the requested privileges with SHOW GRANTS.

Let’s understand it by example:

OutPut screen will be like in below

 SHOW GRANTS FOR user

Here in Show command I have used root user so I have got information.If the user, not a root level, will get the error.The user can see only the information relevant to privileges.If user hasn’t an access, get Error like

Remember: If you are not the root-level user, some of the commands will not be available for you or will display only limited information.

2. SHOW DATABASES [LIKE value]

This command just lists all the databases on your MySQL server.

SHOW DATABASES

SHOW DATABASES have the server host list in MySQL database. You can also use the MySQL Show Command to get the host list. You can see that database of certain privileges unless you have the global SHOW DATABASES privilege.

3. SHOW [OPEN] TABLES [FROM database_name] [LIKE value]

After selecting a database, you can also use SHOW commands to list the tables in the database

If you add OPEN into SHOW TABLES command, you will get a list of all the tables in the table cache, which shows that how many times they’re cached.

opentables SHOW

Remember: If SHOW OPEN TABLES shows that tables are cached numerous times but aren’t currently in use so use FLUSH TABLES to free up that memory. FLUSH tables i have already explained earlier.

4. SHOW CREATE TABLE table_name

A very useful command is SHOW CREATE TABLE which shows the SQL statement used to create a table.

SHOW CREATE TABLE

This Command gives same information that you would get if you dumped the table schema, but the SHOW CREATE TABLE command can be used quickly.

5. SHOW [FULL] COLUMNS FROM table_name [FROM database_name] [LIKE value]

Now, you want to know the structure of the table then you can use SHOW COLUMNS command

SHOW COLUMNS command

The SHOW COLUMNS and DESCRIBE commands are alias of each other.

6. SHOW INDEX FROM table_name [FROM database_name]

The SHOW INDEX command displays information about all the indexes available in a table.

7. SHOW TABLE STATUS [FROM db_name] [LIKE value]

Another helpful command is SHOW TABLE STATUS gives a table full results.

SHOW TABLE STATUS

8. SHOW STATUS/VARIABLES [LIKE value]

The SHOW STATUS and SHOW VARIABLES commands will give important information about the database server.

The Most Useful status variables of SHOW STATUS are

Slow_queries : The number of queries that have taken more than default time that is 10 seconds

Connections : The number of connection attempts to the MySQL server during the current time period.

Max_used_connections : The maximum number of connections that have been in use simultaneously during the current time period.

You may also like:

UDF in MySQL
MySQL FLUSH Commands
MySQL CURSOR Explained

If you like the article, don’t forget to share it on Facebook and Twitter and do subscribe to our newsletter to keep receiving updates.How do you use Show command and which command is more usual for you? Leave me a note in the comments.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

How to save image in MySQL Database

Posted on 12 years ago

Bhumi

The Readers’ Poll – July 2012

Posted on 12 years ago

Bhumi

Learn about Variables in LESS CSS

Posted on 9 years ago

Bhumi

How to use HTML5 Constraint Validation

Posted on 9 years ago

Bhumi

New Features in WordPress 3.5

Posted on 11 years ago

Bhumi