How to View MySQL Table Data and Structure (SHOW TABLES, DESCRIBE, SELECT, INFORMATION_SCHEMA)

1. Introduction

When managing a database with MySQL, there are many situations where you want to “check what’s inside a table.” For example, you may want to verify that test data was stored correctly, or confirm that the expected data is being inserted while developing an application. Being able to quickly inspect table contents is extremely important.

In development and day‑to‑day operations, you also frequently need information such as “What does the table structure look like?” or “What are the column types and constraints?” However, if you’re not familiar with MySQL, it can be surprisingly unclear how to check a table’s contents and structure.

In this article, we’ll explain everything from the basics of checking table contents and structure in MySQL to practical, commonly used commands and advanced techniques, as well as common errors and troubleshooting. This guide includes helpful information for beginners and for those who use MySQL regularly—so please use it as a reference.

2. Prerequisite: How to Connect to MySQL

To check the contents and structure of MySQL tables, you first need to connect to the MySQL server. Here, we explain how to log in to MySQL from the command line (Terminal or Command Prompt) and how to select a database.

How to log in to MySQL

To connect to MySQL, use the following command.

mysql -u username -p

Replace “username” with your MySQL username. When you run the command, you will be prompted to enter a password—type the correct password.

Select a database

After logging in successfully, you will see the MySQL prompt (mysql>). To select the database you want to work with, enter the following command.

USE database_name;

After this, subsequent operations will be executed against the selected database.
If you forget this step, you may get an error such as “No database selected,” so be careful.

How to list databases

If you want to see a list of databases that exist on the server, you can use the following command.

SHOW DATABASES;

This will display all database names in a list. As needed, confirm the target database name here and switch using USE database_name;.

3. Check the Table List (Display a List of Tables)

After connecting to MySQL and selecting the target database, you will often want to confirm “what tables exist.” Here, we introduce the basic method for listing tables, as well as useful techniques to narrow down to only the tables you need.

Basic command to list tables

To display a list of tables, use the following command.

SHOW TABLES;

Running this command displays a list of all table names in the currently selected database.
For example, you might see table names like “users,” “orders,” and “products” listed vertically.

Check the table list in another database

If you want to check the table list of a different database that is not currently selected, write the command like this:

SHOW TABLES FROM database_name;

With this approach, you can quickly check tables in the specified database without switching databases using the USE command each time.

Search (filter) for specific table names

When there are many tables, you may want to find a target table quickly. In that case, you can filter using the LIKE option.

SHOW TABLES LIKE 'search_word%';

For example, if you want to show only tables that start with “user,” specify it like SHOW TABLES LIKE 'user%';.

Also, depending on the MySQL version, you may be able to use a SHOW TABLES WHERE clause.

SHOW TABLES WHERE Tables_in_database_name LIKE '%keyword%';

Listing tables is a fundamental MySQL operation, but a small amount of ingenuity can greatly improve management and search efficiency.

4. Check the Table Structure (List of Columns)

Before checking table contents, it’s very important to understand “which columns exist and what data types and constraints are set.” Knowing the table structure helps you retrieve and edit data correctly, and it also helps prevent issues during system development and operations.

Basic command: DESCRIBE (DESC)

The most common command to check table structure is DESCRIBE. You can also use the shorthand DESC.

DESCRIBE table_name;

or

DESC table_name;

When you run this command, MySQL displays a list of column names, data types, NULL constraints, primary key information, and more.

Main items shown:

• Field (column name)
• Type (data type)
• Null (whether NULL is allowed)
• Key (primary key, foreign key, etc.)
• Default (default value)
• Extra (additional info such as auto-increment)

SHOW COLUMNS command

SHOW COLUMNS FROM table_name; displays almost the same content as DESCRIBE. Depending on the situation, using this command is also fine.

If you want more detailed information

If you need more detailed column information, use SHOW FULL COLUMNS FROM table_name;. This includes additional information such as collation (Collation) and privileges (Privileges).

When table structure checks are useful

• Understand “what columns exist” during new development or maintenance
• Check column types, NULLability, and primary key info when investigating an existing system or performing data integration
• Clarify “what data type will be stored” when writing code

To check table contents safely and accurately, start by confirming the table structure first.

5. Check Table Contents (Data)

Once you understand the table structure, the next step is to check what data is actually stored in the table. In MySQL, you can easily retrieve data from a table using the SELECT statement.

View all data

If you want to display all rows in a table, write the query like this:

SELECT * FROM table_name;

Running this command displays all rows (records) in the table.
However, if the table contains a large amount of data, displaying everything at once can be hard to read—so use caution.

Select only the columns you need

If you don’t need all columns and want to see only specific information, specify the column names you want to retrieve:

SELECT column1, column2 FROM table_name;

This displays only the specified columns, making it easier to review or aggregate data.

Filter data by conditions

If you want to check only data that matches certain conditions, use the WHERE clause:

SELECT * FROM table_name WHERE column_name = 'value';

For example, if you want to check “only the user with id = 10” in the users table, write it like:
SELECT * FROM users WHERE id = 10;

If you want to search using a partial match, use the LIKE operator:

SELECT * FROM table_name WHERE column_name LIKE 'A%';

In this example, only records whose column value starts with “A” will be displayed.

Limit the number of rows returned (LIMIT clause)

If the table contains a very large number of rows, it’s also important to limit how many results are displayed using the LIMIT clause:

SELECT * FROM table_name LIMIT 10;

This displays only the first 10 rows.
In addition, you can use OFFSET to specify which row to start from:

SELECT * FROM table_name LIMIT 10 OFFSET 20;

This retrieves 10 rows starting from the 21st row, which is useful for paging and reviewing large datasets.

6. Get More Detailed Table Information

In MySQL, there are commands not only to check table contents and structure, but also to retrieve more detailed information. Here, we explain how to check table status, creation details, table size, and more.

Check table status (SHOW TABLE STATUS)

If you want an overview of table status and basic information, use the SHOW TABLE STATUS command:

SHOW TABLE STATUS;

Running this command displays a list of information like the following for all tables in the currently selected database:

• Table name (Name)
• Engine (Engine)
• Row count (Rows)
• Table size (Data_length, Index_length)
• Created time (Create_time)
• Last updated time (Update_time)
• Collation (Collation), etc.

If you want to check only a specific table, specify it like this:

SHOW TABLE STATUS LIKE 'table_name';

SQL used to create the table (SHOW CREATE TABLE)

If you want to know “What SQL was used to create this table?”, the SHOW CREATE TABLE command is useful:

SHOW CREATE TABLE table_name;

When executed, it outputs the exact CREATE TABLE statement that was used to create the table.
This command is extremely helpful for table migration, backups, and recreating the same table in another environment.

When detailed table info is useful

• Understand table size and row count for performance improvements and storage management
• Check DDL (table definitions) as reference material for migrations and refactoring
• Investigate engine type, character set, and index information to help identify causes of system issues

By mastering these commands, you can greatly expand what you can do in MySQL operations and troubleshooting.

7. Checking via INFORMATION_SCHEMA (Advanced)

MySQL provides a special database called INFORMATION_SCHEMA. It stores various types of metadata about the entire MySQL server, including table information, column details, indexes, privileges, and more. By using it, you can retrieve table and column information in a more flexible and detailed way.

Retrieve table information using INFORMATION_SCHEMA

For example, if you want to retrieve a list of all table names in a specific database, you can use the following SQL:

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

This SQL is useful when you need to inspect a large number of tables using management tools or collect information automatically with scripts.

Retrieve column and index information

You can retrieve column information in a similar way:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'database_name'
  AND TABLE_NAME = 'table_name';

You can also easily retrieve index details, constraints, foreign keys, and other metadata using INFORMATION_SCHEMA.

Relation to ANALYZE TABLE

For performance tuning and refreshing statistics, the ANALYZE TABLE command is used.
When you run this command, the table statistics are updated, and statistical values retrieved from INFORMATION_SCHEMA (such as estimated row counts) become more accurate.

ANALYZE TABLE table_name;

When to use INFORMATION_SCHEMA

• When you want to retrieve information about multiple tables or columns at once
• When checking table and column structures in batch processing or automation scripts
• When you need detailed information such as indexes, foreign keys, or table comments

INFORMATION_SCHEMA is a powerful tool for gaining a deeper understanding of MySQL and operating it more efficiently. Make full use of it in daily management tasks and system development.

8. CLI Tools and Shortcuts

When checking table contents and structure in MySQL, knowing convenient command-line interface (CLI) commands and options can significantly improve your efficiency. Here, we introduce commonly used CLI tools and helpful shortcuts.

The mysqlshow command

MySQL provides a dedicated CLI tool called mysqlshow.
It is specialized for listing databases and tables and allows you to quickly check information without logging in to MySQL interactively.

Display a list of databases

mysqlshow -u username -p

Display tables in a specific database

mysqlshow -u username -p database_name

Display column information for a specific table

mysqlshow -u username -p database_name table_name

With these short commands, you can quickly check structure and existence, making them convenient for server management and scripting.

Make output easier to read with the “\G” option

Normally, when you execute SQL in MySQL, results are displayed in a horizontal table format. However, if there are many columns or a lot of information, using “\G” displays results vertically, making them much easier to read.

Example usage

SELECT * FROM table_name\G

When written this way, each record is displayed vertically, with column names and values shown in pairs.
This is especially useful when checking table structure or carefully reviewing details of a single record.

Other useful CLI techniques

• Using command history You can use the up and down arrow keys to recall previously executed commands, so you don’t need to type the same command repeatedly.
• Tab completion When typing table or column names, press the Tab key after partially typing the name to auto-complete it, reducing spelling and input errors.

By mastering CLI tools and shortcuts, MySQL operations become more comfortable and efficient. Try incorporating them into your daily work and development tasks.

9. Common Errors and How to Fix Them

When checking table contents and structure in MySQL, there are several common errors that both beginners and experienced users encounter at least once. Here, we clearly explain typical errors, their causes, and how to resolve them.

No database selected

Error message

ERROR 1046 (3D000): No database selected

Cause & Solution
This error occurs when you attempt to operate on tables without selecting a database first.
Be sure to specify the target database in advance using:

USE database_name;

Unknown database

Error message

ERROR 1049 (42000): Unknown database 'database_name'

Cause & Solution
This happens when the specified database does not exist. Common causes include spelling mistakes, incorrect capitalization, or the database not being created yet.
Use the following command to confirm the correct name:

SHOW DATABASES;

Permission errors (no results shown with SHOW TABLES / SHOW COLUMNS)

Example situations

• No tables appear when running SHOW TABLES
• No information appears when running SHOW COLUMNS

Cause & Solution
This occurs when the MySQL user does not have sufficient privileges for the database or table.
Ask an administrator (such as the root user) to grant the necessary permissions, or configure privileges using the appropriate GRANT command.

Too many rows returned, making results hard to read

Example situation

• Running SELECT * FROM table_name; encourages tens of thousands of rows to display at once

Solution
Use the LIMIT clause to restrict the number of rows returned.
Example:

SELECT * FROM table_name LIMIT 10;

Misspelled table or column names

Example situations

• Unknown column ‘column_name’ in ‘field list’
• Table ‘database_name.table_name’ doesn’t exist

Solution
To avoid spelling mistakes, use SHOW TABLES, SHOW COLUMNS, or tab completion features in the CLI.

Most of these errors can be avoided by performing basic checks carefully. When something goes wrong, review the above points step by step.

10. Summary

There are various approaches to the basic operation of “checking table contents” in MySQL. In this article, we covered everything from listing tables and checking detailed table structures to retrieving actual data, as well as obtaining advanced metadata and detailed management information.

• Use SHOW TABLES to list tables,
• Use DESCRIBE (DESC) or SHOW COLUMNS to check column information and structure,
• Use the SELECT statement to retrieve actual data,
• Use SHOW TABLE STATUS , SHOW CREATE TABLE , and INFORMATION_SCHEMA to inspect detailed management information and DDL when needed,
• And improve daily development and operations efficiency with CLI tools, shortcuts, and troubleshooting knowledge.

By mastering these techniques, MySQL database operations become safer and more reliable, and mistakes or issues can be minimized. This content is useful for beginners, intermediate users, and professionals working in real-world environments. Apply these techniques in your future development and system administration tasks.

11. FAQ (Frequently Asked Questions)

Q1. How can I check table data with specific conditions?

A:
Use the WHERE clause like this:

SELECT * FROM table_name WHERE condition;

For example:

SELECT * FROM users WHERE age > 30;

This retrieves only users older than 30. You can also combine it with the LIMIT clause to control how many rows are displayed.

Q2. I ran SHOW TABLES but no tables appear. Why?

A:
Possible causes include:
(1) You did not select a database (USE database_name; is missing),
(2) The specified database name is incorrect,
(3) Insufficient privileges.
Use SHOW DATABASES; to confirm the database name and check whether you have appropriate permissions.

Q3. How can I view the table structure as DDL (CREATE statement)?

A:
Run the following command:

SHOW CREATE TABLE table_name;

It displays the exact CREATE TABLE statement used to create the table. This is useful for table migration and backups.

Q4. There is too much data in the table to view all at once.

A:
Use the LIMIT clause:

SELECT * FROM table_name LIMIT 10;

You can also combine it with OFFSET to check specific ranges step by step.

Q5. What is INFORMATION_SCHEMA and when should I use it?

A:
INFORMATION_SCHEMA is a special database that stores MySQL metadata. It is very useful when you need to retrieve or manage large amounts of information about tables, columns, indexes, or privileges. Since it can be queried directly with SQL, it is also ideal for automation and reporting.

Q6. How can I display results vertically?

A:
Add “\G” at the end of your SQL statement.
Example:

SELECT * FROM table_name\G

This displays each record vertically, showing column names and values in a readable pair format.