Getting Information About MySQL Databases and Tables

It is possible for the user to forget the name of the database or table or the structure of table or the name of the columns. This issue can be solved using MySQL since it supports many statements that provide information about the databases and tables which it supports.

The ‘SHOW DATABASES’ query can be used to list all the databases that are managed by the server. To see which database is currently in use, the ‘DATABASE()’ function.

Let us understand this query in the below section −

Query

mysql> SELECT DATABASE();

Output

+---------------------+
| DATABASE()          |
+---------------------+
| databaseInUse       |
+---------------------+

If no database is selected, it results in output ‘NULL’.

To see which tables the default database contains, the below query can be used −

Query

mysql> SHOW TABLES;

Output

+-----------------------------------+
| Tables_in_databaseInUse           |
+-----------------------------------+
| val1                              |
| val1                              |
+-----------------------------------+

The column name in the output which is produced by the above query is ‘Tables_in_databaseInUse’, where databaseInUse is the name of the database that is in use/selected.

If the user wants to know more information about the structure of the table, the ‘DESCRIBE’ statement can be used. It will display the information about every table’s columns −

Query

mysql> DESCRIBE pet;

Output

+---------+-------------+-------+------+-----------+--------+
| Field   | Type        | Null  | Key  | Default   | Extra  |
+---------+-------------+-------+------+-----------+--------+
| name    | varchar(20) | YES   |      | NULL      |        |
| owner   | varchar(20) | YES   |      | NULL      |        |
+---------+-------------+-------+------+-----------+--------+

The field indicates the column name, ‘Type’ refers to the data type of the column, ‘NULL’ indicates if the column can contain NULL values or not, ‘Key’ refers to whether the column is indexed or not, and ‘Default’ specifies the column’s default value. The ‘Extra’ displays special information about the columns. If a column was created using the ‘AUTO_INCREMENT’ option, the value is ‘auto_increment’, not empty.