JIYIK CN >

Today, we will learn about queries in MySQL that can display the table and database structure. We will use the mysqldump utility, DESCRIBEthe , SHOW TABLESand SHOW CREATE TABLEthe statements.

We are using MySQL version 8.0.28 while writing this tutorial.

Display table structure in MySQL

We can use different ways to get the table structure in MySQL according to our requirement. Some of them are given below.

Using the mysqldump utility in MySQL

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

Sample code:

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

For a specific table, we can add the name of the table after the database name, or use SHOW CREATE TABLEthe statement as shown below to achieve a similar result.

Sample 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 the list of columns discussed below.

Using the DESCRIBE (table) statement in MySQL

Sample code:

DESCRIBE courses;

We have to log in to MySQL server to use DESCRIBE statement. It displays the column list of a particular table such as attribute (field) names, their data types, default values, keys, etc.

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

Sample code:

SHOW TABLES;

Use mysqldump to display the database structure in MySQL

We use the command line tool called mysqldump to dump or create a copy or backup of the MySQL database. While using the mysqldump command, we use multiple flags as required.

For example, if we do not want to include the data while copying the database structure, we can use the --no-data flag. Let's use mysqldump to get the database structure only by considering the following scenario.

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

If you installed MySQL server in the default location, you must be in C:\Program Files\MySQL\MySQL Server 8.0\bin>the directory to use the following commands.

In the following commands, we have used various flags which you must understand before using these commands.

1. The -u flag indicates the user name.
2. -p flat means that the password will be provided when asked (you can also type the password using the -p flag without spaces, for example -p12345, but this is not recommended for security reasons).
3. If we don't need to include data, we use --no-data. Alternatively, we can also use -d here.
4. If we want to backup one or more databases, use --database.
5. We use --all-databases to back up all databases at once.
6. The greater than sign (>) saves the backup file. To restore, you can use the less than sign (<).

Sample code (only gets the database structure of one table):

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

Sample code (only get the database structure of multiple tables):

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

Sample code (only get the database structure of 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 the database structure to standard output. This means that we will have to scroll up and down in the terminal (command line window), which may not be helpful.

We can save the backup without data to a file using >. We can also do this using the command given above.

Sample code (get the database structure of all databases into one 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.