Displaying Databases and Tables in MySQL

Course: MySQL Training

Topic: Displaying Databases and Tables

Learn how to display databases, list tables, filter database and table names, inspect table structures, view database definitions, and retrieve metadata using MySQL commands.

Course Link: JustAcademy MySQL Training Course

Demo Link: Register for MySQL Course Demo

1. Learning Objectives

After completing this topic, learners will be able to:

• Understand how databases and tables are organized in MySQL.
• Display databases using SHOW DATABASES.
• Display tables using SHOW TABLES.
• List tables from a specific database without selecting it first.
• Filter database and table names using the LIKE clause.
• Display the currently selected database.
• Inspect table columns using DESCRIBE and SHOW COLUMNS.
• View database and table definitions using SHOW CREATE commands.
• Retrieve metadata through the INFORMATION_SCHEMA database.
• Troubleshoot common database and table visibility issues.

2. Introduction to Displaying Databases and Tables

MySQL provides several SQL statements for viewing information about databases, tables, columns, and other database objects. These commands help users understand the structure of a database server and locate the information they need.

For example, if you are working on a student management application, you may want to check whether the database exists, which tables it contains, and what columns are defined in the students table.

Example Database Structure

student_management_db
├── students
├── courses
├── trainers
├── enrollments
└── payments

This is an example structure. The database and tables must already exist before they can appear in the corresponding listing commands.

Common Commands

3. Displaying All Databases Using SHOW DATABASES

The SHOW DATABASES statement displays the databases on a MySQL server that are visible to the connected account. It is commonly used to check whether a database has been created successfully.

Syntax

SHOW DATABASES;

Example

SHOW DATABASES;

Sample Output

+----------------------+
| Database             |
+----------------------+
| college_db           |
| information_schema   |
| mysql                |
| performance_schema   |
| student_management_db|
+----------------------+

Note: This is illustrative output. The actual databases displayed depend on your MySQL server, configuration, and account privileges.

Explanation

• SHOW: Requests information from the MySQL server.
• DATABASES: Specifies that database names should be listed.
• Semicolon: Terminates the statement in typical MySQL command-line usage.

When Is SHOW DATABASES Useful?

• Checking whether a database exists.
• Reviewing databases available to your account.
• Verifying database creation after running CREATE DATABASE.
• Finding the correct database name before selecting it.
• Troubleshooting database-name errors.

4. Understanding SHOW SCHEMAS

In MySQL, SHOW SCHEMAS is a synonym for SHOW DATABASES. Both statements list the databases visible to the connected account.

Example

SHOW SCHEMAS;

The result is equivalent to running:

SHOW DATABASES;

Although both commands work, beginners can consistently use SHOW DATABASES because it clearly describes the operation.

5. Displaying Tables Using SHOW TABLES

The SHOW TABLES statement lists the non-temporary tables and also the views in the selected database. You generally need to select a database first unless you specify the database name directly in the statement.

Syntax

SHOW TABLES;

Example

USE student_management_db;
SHOW TABLES;

Sample Output

+-------------------------------+
| Tables_in_student_management_db |
+-------------------------------+
| courses                       |
| enrollments                   |
| payments                      |
| students                      |
| trainers                      |
+-------------------------------+

The output column name typically includes the selected database name, such as Tables_in_student_management_db.

Important Points

• SHOW TABLES lists objects in the selected database.
• The database must exist.
• The output depends on the privileges of the connected account.
• If the database has no visible tables or views, the result may be empty.
• Views can also appear in the listing.

6. Displaying Tables from a Specific Database

You do not always need to select a database using USE before listing its tables. You can specify the database name directly with FROM or IN.

Syntax Using FROM

SHOW TABLES FROM database_name;

Example

SHOW TABLES FROM student_management_db;

Syntax Using IN

SHOW TABLES IN database_name;

Example

SHOW TABLES IN college_db;

In this context, FROM and IN are alternative ways to specify the database whose tables should be displayed.

When Should You Use These Commands?

• When checking another database without changing the current selection.
• When comparing table names across databases.
• When troubleshooting a missing table.
• When preparing database administration scripts.

7. Checking the Currently Selected Database

The SELECT DATABASE() function returns the name of the database currently selected for the connection. If no database is selected, it returns NULL.

Syntax

SELECT DATABASE();

Example

USE student_management_db;
SELECT DATABASE();

Sample Output

+-------------------------+
| DATABASE()              |
+-------------------------+
| student_management_db   |
+-------------------------+

Checking Before and After USE

SELECT DATABASE();

USE college_db;

SELECT DATABASE();

The first query displays the current database, if one is selected. The second statement selects college_db, and the final query confirms the selection.

8. Filtering Database Names Using LIKE

The LIKE clause filters database names based on a pattern. It is useful when a server contains many databases and you want to find names that follow a particular naming convention.

Example 1: Find Databases Starting with student

SHOW DATABASES LIKE 'student%';

The percent sign (%) matches zero or more characters.

Example 2: Find Databases Ending with db

SHOW DATABASES LIKE '%db';

Example 3: Find Databases Containing the Word college

SHOW DATABASES LIKE '%college%';

Understanding LIKE Wildcards

For example, SHOW DATABASES LIKE 'test_%'; matches database names beginning with test_ followed by at least one character. Pattern matching behavior may depend on server settings and collation rules.

9. Filtering Table Names Using LIKE

You can also use LIKE with SHOW TABLES to find tables that match a particular naming pattern.

Example 1: Find Tables Starting with student

SHOW TABLES LIKE 'student%';

Example 2: Find Tables Ending with details

SHOW TABLES LIKE '%details';

Example 3: Find Tables Containing the Word course

SHOW TABLES LIKE '%course%';

Example 4: Search in a Specific Database

SHOW TABLES FROM student_management_db
LIKE 'student%';

These commands help locate tables without displaying every table name in the database.

10. Displaying Table Types Using SHOW FULL TABLES

The SHOW FULL TABLES statement displays table names and an additional column identifying the object type. Depending on the object, the type can include BASE TABLE or VIEW.

Syntax

SHOW FULL TABLES;

Example

USE student_management_db;
SHOW FULL TABLES;

Sample Output

+-------------------+------------+
| Tables_in_database| Table_type |
+-------------------+------------+
| students          | BASE TABLE |
| active_students   | VIEW       |
+-------------------+------------+

This is sample output. The actual column heading contains the selected database name, and the listed objects depend on what exists and what the account can access.

Difference Between SHOW TABLES and SHOW FULL TABLES

11. Displaying Table Columns Using DESCRIBE

After finding a table, you may need to inspect its structure. The DESCRIBE statement, also written as DESC, displays information about the table's columns.

Syntax

DESCRIBE table_name;

Example

DESCRIBE students;

Sample Output

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| student_id     | int          | NO   | PRI | NULL    | auto_increment |
| student_name   | varchar(100) | NO   |     | NULL    |                |
| email          | varchar(150) | YES  | UNI | NULL    |                |
| course_name    | varchar(100) | YES  |     | NULL    |                |
| enrollment_date| date         | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

The output above is illustrative and assumes a table with the shown column definitions.

Explanation of DESCRIBE Columns

Using DESC as a Shortcut

DESC students;

This is a shorter form of DESCRIBE and provides the same type of column information.

12. Displaying Columns Using SHOW COLUMNS

The SHOW COLUMNS statement also displays information about the columns in a table.

Syntax

SHOW COLUMNS FROM table_name;

Example

SHOW COLUMNS FROM students;

Display Columns from a Specific Database

SHOW COLUMNS FROM students
FROM student_management_db;

You can use SHOW FULL COLUMNS when you need additional details, such as collation, privileges, and comments, depending on the table definition and server output.

SHOW FULL COLUMNS FROM students;

DESCRIBE vs SHOW COLUMNS

13. Viewing the SQL Definition of a Database

The SHOW CREATE DATABASE statement displays the SQL definition associated with an existing database, including applicable character-set and collation settings.

Syntax

SHOW CREATE DATABASE database_name;

Example

SHOW CREATE DATABASE student_management_db;

This command helps you inspect the database's definition and default settings.

When Is It Useful?

• Checking a database's default character set.
• Checking its default collation.
• Reviewing configuration before migrating or documenting a database.
• Comparing settings across environments.

14. Viewing the SQL Definition of a Table

The SHOW CREATE TABLE statement displays the SQL definition used to create a table. It can reveal column definitions, indexes, constraints, storage-engine details, and other table options.

Syntax

SHOW CREATE TABLE table_name;

Example

SHOW CREATE TABLE students;

Example with a Database Name

SHOW CREATE TABLE student_management_db.students;

Using the database-qualified table name helps identify exactly which table definition you want to inspect.

DESCRIBE vs SHOW CREATE TABLE

15. Using INFORMATION_SCHEMA to Display Database Information

INFORMATION_SCHEMA is a built-in MySQL database containing metadata about databases, tables, columns, and other objects. It allows users to query metadata using SELECT statements.

15.1 Display Database Names

SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA;

This query returns database names visible through the metadata query's applicable privileges and server rules.

15.2 Display Tables in a Specific Database

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'student_management_db';

15.3 Display Table Names and Types

SELECT TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'student_management_db';

The TABLE_TYPE column can distinguish base tables from views.

15.4 Display Column Names and Data Types

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'student_management_db'
AND TABLE_NAME = 'students';

SHOW Commands vs INFORMATION_SCHEMA

16. Displaying Databases and Tables in MySQL Workbench

MySQL Workbench offers a graphical way to view databases and tables in addition to executing SQL statements.

Steps to Display Databases

1. Open MySQL Workbench.
2. Connect to your MySQL server.
3. Locate the Schemas panel.
4. Review the available database names.
5. Refresh the Schemas panel if recently created databases are not visible.

Steps to Display Tables

1. Expand the required database in the Schemas panel.
2. Locate the Tables section.
3. Expand Tables to view the available table names.
4. Right-click a table to access available options, such as viewing its structure or selecting rows.

Using the SQL Editor

You can also enter and execute the following commands in a query tab:

SHOW DATABASES;

USE student_management_db;

SHOW TABLES;

DESCRIBE students;

17. Practical Example: Student Management Database

In this practical exercise, you will create a database, add tables, and display database and table information.

Step 1: Create a Database

CREATE DATABASE IF NOT EXISTS student_management_db;

Step 2: Display Databases

SHOW DATABASES;

Step 3: Select the Database

USE student_management_db;

Step 4: Create a Students Table

CREATE TABLE IF NOT EXISTS students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    course_name VARCHAR(100),
    enrollment_date DATE
);

Step 5: Create a Courses Table

CREATE TABLE IF NOT EXISTS courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    duration_months INT
);

Step 6: Display All Tables

SHOW TABLES;

Step 7: Display Tables Using the Database Name

SHOW TABLES FROM student_management_db;

Step 8: Display the Structure of Students

DESCRIBE students;

Step 9: Display the Table Definition

SHOW CREATE TABLE students;

Step 10: Check the Selected Database

SELECT DATABASE();

Step 11: Query Table Metadata

SELECT TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'student_management_db';

Expected result: You should be able to identify the database, view its tables, inspect the students table's columns, and retrieve metadata about the database objects.

18. Common Errors and Troubleshooting

Troubleshooting Example

If this command does not show the expected tables:

SHOW TABLES;

Check the current database:

SELECT DATABASE();

If the result is NULL or shows the wrong database, select the intended database and try again:

USE student_management_db;
SHOW TABLES;

19. Best Practices

• Run SHOW DATABASES before selecting an unfamiliar database.
• Use SELECT DATABASE() to confirm the current selection.
• Use SHOW TABLES FROM database_name to inspect another database without changing the current selection.
• Use LIKE to find objects by naming patterns.
• Use DESCRIBE for a quick view of table columns.
• Use SHOW CREATE TABLE when you need to review the table's SQL definition.
• Use INFORMATION_SCHEMA for structured metadata queries and reporting.
• Remember that database and table listings are affected by privileges.
• Use clear and consistent names for databases and tables.
• Avoid sharing database credentials or granting unnecessary privileges.

20. Practice Exercises

Complete the following exercises using MySQL Workbench or the MySQL command-line client.

1. Display all databases visible to your account.
2. Display the databases using SHOW SCHEMAS.
3. Select the student_management_db database.
4. Check the currently selected database.
5. Display all tables in the selected database.
6. Display tables from college_db without selecting it.
7. Find database names beginning with student.
8. Find table names containing the word course.
9. Display table names and their types using SHOW FULL TABLES.
10. Display the columns of the students table using DESCRIBE.
11. Display the column information using SHOW COLUMNS.
12. Inspect the SQL definition of the students table.
13. Use INFORMATION_SCHEMA to list tables in the student management database.
14. Use INFORMATION_SCHEMA to list the column names and data types of the students table.

Sample Solutions

SHOW DATABASES;

SHOW SCHEMAS;

USE student_management_db;

SELECT DATABASE();

SHOW TABLES;

SHOW TABLES FROM college_db;

SHOW DATABASES LIKE 'student%';

SHOW TABLES LIKE '%course%';

SHOW FULL TABLES;

DESCRIBE students;

SHOW COLUMNS FROM students;

SHOW CREATE TABLE students;

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'student_management_db';

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'student_management_db'
AND TABLE_NAME = 'students';

21. Interview Questions and Answers

Q1. Which command displays databases in MySQL?

SHOW DATABASES; lists the databases visible to the current account.

Q2. What is the purpose of SHOW TABLES?

It lists the tables and views in the selected database.

Q3. Can you display tables without selecting a database first?

Yes. Use SHOW TABLES FROM database_name; or SHOW TABLES IN database_name;.

Q4. What is the difference between SHOW DATABASES and SHOW TABLES?

SHOW DATABASES lists database names, while SHOW TABLES lists tables and views in a particular database.

Q5. How do you find the current database?

SELECT DATABASE();

Q6. What does DESCRIBE do?

It displays information about a table's columns, data types, keys, nullability, defaults, and additional attributes.

Q7. What is the difference between DESC and DESCRIBE?

DESC is a short form of DESCRIBE.

Q8. How can you display only tables beginning with the word student?

SHOW TABLES LIKE 'student%';

Q9. What is SHOW FULL TABLES used for?

It lists tables and views with an additional column indicating the object type.

Q10. How do you display a table's SQL definition?

SHOW CREATE TABLE students;

Q11. What is INFORMATION_SCHEMA?

It is a built-in MySQL database containing metadata about databases, tables, columns, and other objects.

Q12. Why might a database not appear in SHOW DATABASES?

The connected account may not have privileges to see that database, or the database may not exist on the connected server.

Q13. Why might SHOW TABLES return an empty result?

The selected database may contain no visible tables or views, or the account may not have permission to view the relevant objects.

Q14. What is the difference between SHOW COLUMNS and SHOW CREATE TABLE?

SHOW COLUMNS displays column information, while SHOW CREATE TABLE displays the table's SQL definition.

Q15. How do you list tables using INFORMATION_SCHEMA?

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'student_management_db';

22. Quick Revision Cheat Sheet

23. Official MySQL Documentation

• MySQL Documentation: SHOW DATABASES
• MySQL Documentation: SHOW TABLES
• MySQL Tutorial: Getting Information About Databases and Tables
• MySQL Documentation: SHOW Statements

24. Enroll in MySQL Training

Strengthen your database skills by learning SQL commands, database management, table creation, data retrieval, joins, constraints, and practical MySQL projects.

Explore the JustAcademy MySQL Training Course

Interested in learning more before enrolling? Register for a course demo.

Register for a MySQL Course Demo

Conclusion

Displaying databases and tables is an essential skill for working with MySQL. SHOW DATABASES helps you find available databases, SHOW TABLES lists objects within a database, and commands such as DESCRIBE and SHOW CREATE TABLE help you understand table structures. By practising these commands and learning to query INFORMATION_SCHEMA, you can inspect database objects efficiently and troubleshoot common database-management issues.