How to Show Table and Database Structure in MySQL

1. Show Table Structure in MySQL
2. Use mysqldump to Show Database Structure in MySQL

Today, we will learn about queries that can show the table and database structure in MySQL. We will be using the mysqldump utility, DESCRIBE, SHOW TABLES, and SHOW CREATE TABLE statements.

We used MySQL 8.0.28 version while writing this tutorial.

Show Table Structure in MySQL

We can use different ways to get the table structure in MySQL based on requirements. Some of them are given below.

Use the mysqldump Utility in MySQL

We can use the mysqldump command-line tool to have a complete database structure as a set of all the CREATE TABLE statements.

Example code:

# The `--compact` flag produces the compact output
mysqldump -u username -p ms23 --compact --no-data

For a particular table, we can add the table’s name after the database’s name or use the SHOW CREATE TABLE statement as follows to get similar results.

Example code:

# To use this, you must be logged in to MySQL Server and a database.
SHOW CREATE TABLE courses;

We can use the DESCRIBE statement to get a column listing discussed below.

Use the DESCRIBE (Table) Statement in MySQL

Example code:

DESCRIBE courses;

We must be logged in to MySQL Server to use the DESCRIBE statement. It shows the column listing of a particular table, such as the attribute (field) names, their data types, default values, keys, etc.

Alternatively, we can use it as DESC courses; or EXPLAIN courses; and get the same results. If we forget the table name, we can use the SHOW TABLES; statement to get a list of tables in a specific database.

Example code:

SHOW TABLES;

Use mysqldump to Show Database Structure in MySQL

We use a command-line tool named mysqldump to dump or create MySQL database copies or backups. While using the mysqldump command, we use multiple flags depending on the requirements.

For instance, we use the --no-data flag if we do not want to include data while copying the database structure. We use the mysqldump to get database structure only by considering the scenarios below.

• Get database structure only for one table
• Get database structure only for multiple tables
• Get database structure only for one or multiple databases
• Get the database structure of all databases into a file

To use the following commands, you must be in the C:\Program Files\MySQL\MySQL Server 8.0\bin> directory if you have installed MySQL Server on the default location.

In the following commands, we use various flags that you must understand before using the commands.

1. The -u flag indicates the username.
2. The -p flat shows that a password will be provided when asked (You can also type the password with the -p flag without space, for instance, -p12345, but it is not recommended for security reasons).
3. We use --no-data if we do not need to include data. Alternatively, we can also use -d here.
4. The --database is used if we want to get a backup for one/multiple databases.
5. We use the --all-databases to backup all databases at once.
6. The greater than (>) symbol can save the backup file. To restore, you can use the less than (<) symbol.

Example code (Get Database Structure Only for One Table):

mysqldump -u username -p --no-data DatabaseName TableName;

Example code (Get Database Structure Only for Multiple Tables):

mysqldump -u username -p --no-data DatabaseName TableName1 TableName2 TableNameN;

Example code (Get Database Structure Only for One/Multiple Databases):

# `-d` is used as an alternative to the `--no-data` option
mysqldump -u username -p -d --databases DatabaseName

We have learned how to dump out the database structure to standard output. It means we will have to scroll up and down on the terminal (command line window), which might not be helpful.

We can use > to save the backup without data into a file. We can do that with the commands given above as well.

Example code (Get Database Structure of All Databases Into a File):

mysqldump -u username -p --no-data --all-databases > E:\\Databases_Structure.sql

If you want to dump a remote database, read this article to find all the necessary information.