How to Check MySQL Table Contents and Structure: Basics to Tips

1. Introduction

When managing databases with MySQL, there are often times when you want to “check the contents of a table.” For example, when verifying if test data is stored correctly or during application development to confirm if the expected data is entered, being able to quickly check the table’s contents is extremely important.

Additionally, in development environments or operational settings, information like “What is the table’s structure?” or “What are the column types and constraints?” is frequently needed. However, for those not familiar with MySQL, it can be surprisingly unclear how to check the contents or structure of a table.

This article explains, in an easy-to-understand way, from basic methods to check table contents and structures in MySQL, to convenient commands commonly used in practice, advanced techniques, and even common errors and troubleshooting. It’s compiled with useful information not only for beginners but also for those who use MySQL regularly, so please refer to it.

2. Prerequisites: How to Connect to MySQL

To check the contents or structure of tables in MySQL, you first need to connect to the MySQL server. Here, we explain the steps 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

For “username”, enter your own MySQL username. When you execute the command, you will be prompted for a password, so enter the correct password.

Selecting a Database

Upon successful login, the MySQL prompt (mysql>) will be displayed. To select the database you want to operate on here, enter the following command.

USE database_name;

This way, subsequent operations will be executed targeting the specified database.
If you forget this operation, an error such as “No database selected” will occur, so please be careful.

How to Check the List of Databases

If you want to check the list of databases currently existing on the server, you can use the following command.

SHOW DATABASES;

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

3. Checking the Table List (Displaying the List)

After connecting to the MySQL database and selecting the target database, there are often situations where you first want to check “what tables exist.” Here, we introduce basic methods for displaying the table list and convenient techniques for narrowing down to only the necessary tables.

Basic Command for Table List

To display the table list, use the following command.

SHOW TABLES;

Executing this command displays a list of all table names existing in the currently selected database.
For example, table names like “users,” “orders,” and “products” will be displayed vertically.

Checking the Table List of Other Databases

If you want to check the table list of another database that is not currently selected, write it as follows.

SHOW TABLES FROM database_name;

This method allows you to immediately check the table list in the specified database without switching databases using the “USE” command each time.

Searching for Specific Table Names (Narrowing Down)

If there are a large number of tables, you may want to quickly find the target table. In that case, you can narrow it down using the LIKE option.

SHOW TABLES LIKE 'search_word%';

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

Additionally, depending on the MySQL version, you can also use the SHOW TABLES WHERE clause.

SHOW TABLES WHERE Tables_in_database_name LIKE '%keyword%';

In this way, displaying the table list is a basic MySQL operation, but with a little ingenuity, the efficiency of management and searching can be greatly improved.

4. Checking the Table Structure (List of Columns)

Before checking the contents of the table, it is very important to understand what columns (fields) exist and what data types or constraints are set. Knowing the table structure helps in correctly retrieving or editing data, and also prevents issues during system development and operation.

Basic Command: DESCRIBE (DESC)

The most common command to check the table structure is DESCRIBE. It can also be used in abbreviated form as DESC.

DESCRIBE table_name;

or

DESC table_name;

Executing this command displays a list of the target table’s column names, data types, NULL constraints, primary key information, and more.Main Items Displayed:

• Field (Column Name)
• Type (Data Type)
• Null (Whether NULL is Allowed)
• Key (Primary Key, Foreign Key, etc.)
• Default (Default Value)
• Extra (Additional Information such as Auto Increment)

SHOW COLUMNS Command

SHOW COLUMNS FROM table_name; also displays almost the same content as DESCRIBE. In some cases, you can use this command without any issues.

If You Want More Detailed Information

If you want to know more details about the columns, using SHOW FULL COLUMNS FROM table_name; will display additional information including collation order (Collation) and privileges (Privileges).

Use Cases for Checking Table Structure

• Grasping what columns exist during new development or maintenance
• Checking column types, NULL allowance, and primary key information during investigations of existing systems or data integration
• Clarifying what data types are used when writing code

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

5. Checking the Contents (Data) of the Table

Once you’ve grasped the table structure, next, let’s check what kind of data is actually stored. In MySQL, you can easily retrieve data from the table using the SELECT statement.

Checking All Data

If you want to display all records in the table, write it like this.

SELECT * FROM table_name;

Executing this command will display all rows (records) in that table in a list.
However, if the table has a large amount of data, be careful as displaying all at once can make it hard to view.

Extracting Only the Necessary Columns

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

SELECT column1, column2 FROM table_name;

This way, only the specified columns are displayed, making it easier to check and aggregate the data.

Filtering Data by Specifying Conditions

If you want to check only data that matches specific conditions, use the WHERE clause.

SELECT * FROM table_name WHERE column_name = 'value';

For example, to check only the user with id 10 in the users table, write SELECT * FROM users WHERE id = 10;.

Also, if you want to search with partial matching, use the LIKE operator.

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

In this example, only records where the column value starts with ‘A’ will be displayed.

Limiting the Number of Retrieved Records (LIMIT Clause)

If the number of data records is very large, it’s important to limit the display count using the LIMIT clause.

SELECT * FROM table_name LIMIT 10;

This way, you can display only the first 10 records.
Furthermore, using OFFSET, you can also specify from which record to start retrieving.

SELECT * FROM table_name LIMIT 10 OFFSET 20;

This allows you to retrieve only 10 records starting from the 21st, which is useful for pagination or checking large amounts of data.

6. Retrieving Detailed Information Around Tables

MySQL provides several commands not only for viewing table contents and structures but also for checking more detailed information. In this section, we’ll explain how to investigate table status, creation information, table sizes, and more.

Checking Table Status (SHOW TABLE STATUS)

When you want to check the table status and basic information all at once, use the SHOW TABLE STATUS command.

SHOW TABLE STATUS;

Executing this command displays a list of the following information for all tables in the currently selected database.

• Table Name (Name)
• Engine (Engine)
• Number of Rows (Rows)
• Table Size (Data_length, Index_length)
• Creation Date and Time (Create_time)
• Last Update Date and Time (Update_time)
• Collation (Collation), etc.

If you want to check only a specific table, specify it as follows.

SHOW TABLE STATUS LIKE 'table_name';

SQL Used to Create the Table (SHOW CREATE TABLE)

When you want to know “What SQL was used to create this table?”, the SHOW CREATE TABLE command is convenient.

SHOW CREATE TABLE table_name;

Executing it outputs the CREATE TABLE statement used to create the table as is.
This command is also very useful for migrating tables, backups, or creating the same table in other environments.

Use Cases for Table Detailed Information

• Understand table sizes and row counts to utilize for performance improvements and storage management
• Check DDL (table definitions) and use as materials for migration or refactoring
• Investigate table engines, character sets, and index information to identify causes of system issues

By mastering these commands, the scope of MySQL operations and troubleshooting greatly expands.

7. Checking from the Information Schema (Advanced)

MySQL provides a special database called “INFORMATION_SCHEMA (Information Schema)”. It stores various metadata such as table information, column information, indexes, permissions, and more for the entire MySQL server. By utilizing this, you can obtain table and column information more flexibly and in detail.

Obtaining Table Information Using INFORMATION_SCHEMA

For example, if you want to obtain a list of all table names in a specific database, use the following SQL statement.

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

This SQL is useful when you want to check a large number of tables at once with a management tool or automatically collect information with a script.

Obtaining Column Information and Index Information

Column information for tables can also be obtained in the same way.

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

Index information, constraints, foreign key information, and more can also be easily obtained using INFORMATION_SCHEMA.

Relation to ANALYZE TABLE

The ANALYZE TABLE command is used for performance tuning and refreshing statistical information.
Using this command updates the table’s statistical information to the latest state, making the statistical values (such as row count estimates) obtained from INFORMATION_SCHEMA more accurate.

ANALYZE TABLE table_name;

Use Cases for INFORMATION_SCHEMA

• When you want to obtain information for multiple tables or columns at once
• When you want to check table or column structures in batch processing or automation scripts
• When you want to grasp detailed information such as indexes, foreign keys, table comments, etc.

INFORMATION_SCHEMA is a powerful tool for understanding MySQL more deeply and operating it efficiently. Let’s actively utilize it in daily management tasks and system development.

8. Introduction to CLI Tools and Shortcuts

When checking the contents or structure of tables in MySQL, knowing convenient commands and options usable in the command-line interface (CLI) will greatly improve work efficiency. Here, we introduce commonly used CLI tools and convenient shortcuts to know.

mysqlshow Command

MySQL comes standard with a dedicated CLI tool called “mysqlshow”.
It is a command specialized for displaying lists of databases or tables, and its feature is that you can quickly check information without logging into MySQL directly.Displaying Database List

mysqlshow -u username -p

Displaying Table List in a Specific Database

mysqlshow -u username -p database_name

Displaying Column Information for a Specific Table

mysqlshow -u username -p database_name table_name

This way, you can quickly check the structure or existence with short commands, making it convenient for server management or scripting purposes as well.

The “G” Option to Make Output Easier to Read

Normally, when executing SQL in MySQL, the results are displayed in a horizontal table format, but when there are many columns or a lot of information, using “G” displays it vertically, making it much easier to read.Example of Use

SELECT * FROM table_name G

Writing it like this displays each record vertically one by one, making it easy to see column names and values in pairs.
It is especially useful when checking table structures or when you want to carefully examine the details of a single record.

Other Convenient CLI Usage Methods

• Using Command History
  You can recall previously entered commands using the up and down keys, so you don’t need to enter the same command multiple times.
• Tab Completion
  For table names or column names, if you enter part of them and press the Tab key, it auto-completes, reducing spelling or input errors.

By mastering CLI tools and shortcuts, MySQL operations become more comfortable and efficient. Please try using them in your daily work or development environment.

9. Common Errors and Solutions

When checking the contents or structure of tables in MySQL, there are several “common errors” that everyone from beginners to experienced users encounters at least once. Here, we will explain typical errors, their causes, and solutions in an easy-to-understand manner.

No database selected

Error Description

ERROR 1046 (3D000): No database selected

Cause and SolutionThis error occurs when you try to perform table operations without selecting a database.
Always specify the target database first with the “USE database_name;” command.

Unknown database

Error Description

ERROR 1049 (42000): Unknown database 'database_name'

Cause and SolutionThis error occurs when the specified database name does not exist. The main causes are spelling mistakes, differences in uppercase and lowercase letters, or the database not being created yet.
Use the “SHOW DATABASES;” command to check for existence, then enter the correct database name.

Permission Error (Results Not Displayed for SHOW TABLES / SHOW COLUMNS, etc.)

Error Example

• No list is displayed even when executing SHOW TABLES
• Information cannot be obtained even when executing SHOW COLUMNS

Cause and SolutionThis occurs when the MySQL user does not have sufficient permissions for the relevant database or table.
In this case, request the administrator (such as the root user) to grant permissions, or set the permissions using the appropriate GRANT command.

Results Are Hard to View Due to Too Many Retrieved Rows

Example Phenomenon

• When executing SELECT * FROM table_name;, tens of thousands or more rows are displayed at once

SolutionUse the “LIMIT clause” to limit the number of displayed rows.
Example: SELECT * FROM table_name LIMIT 10; etc.

Spelling Mistakes in Table Names and Column Names

Example Phenomenon

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

SolutionTo avoid spelling mistakes, make use of SHOW TABLES, SHOW COLUMNS, or tab completion features.

Such errors and troubles can mostly be avoided if you don’t neglect basic checks. When a problem occurs, it’s important to first check the above points one by one.

10. Summary / Article Summary

In MySQL, there are various approaches to the basic operation of “checking the contents of a table.” In this article, we have covered a wide range from confirming the list of tables, detailed checks of table structures, methods to obtain actual data, and even techniques for obtaining detailed information and advanced metadata.

• Check the list of tables with SHOW TABLES,
• Check column information and structure with DESCRIBE (DESC) or SHOW COLUMNS,
• Retrieve actual data with SELECT statements,
• As needed, use SHOW TABLE STATUS, SHOW CREATE TABLE, or INFORMATION_SCHEMA to understand detailed management information and DDL,
• Furthermore, adding knowledge of CLI tools, shortcuts, and troubleshooting will significantly improve daily development and operations efficiency.

By mastering this knowledge, MySQL database operations will become safer and more reliable, minimizing mistakes and troubles. This content is useful from beginners to intermediate users and in business settings, so please make use of it in your future tasks and system management scenarios.

11. FAQ (Examples of Common Questions)

Q1. How can I check the contents of a table under specific conditions?

A:SELECT * FROM table_name WHERE condition; like this, you can use the WHERE clause to extract only data that meets specific conditions. For example, SELECT * FROM users WHERE age > 30; retrieves only users whose age is greater than 30. You can also combine it with the LIMIT clause to adjust the number of displayed results.

Q2. Why doesn’t SHOW TABLES display any tables?

A: Possible causes include: (1) the database is not selected (missing USE database_name;), (2) the specified database name is incorrect, (3) insufficient permissions, and so on. Use SHOW DATABASES; to reconfirm the database name and check if you have sufficient permissions.

Q3. How can I check the table structure using DDL (CREATE statement)?

A:Executing SHOW CREATE TABLE table_name; displays the exact SQL statement (CREATE TABLE statement) used to create that table. This is useful for obtaining the table definition as is, or for table migration and backups.

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

A:Use the LIMIT clause to restrict the number of retrieved rows, like SELECT * FROM table_name LIMIT 10;. Additionally, combining it with OFFSET allows you to check specific ranges step by step.

Q5. What is INFORMATION_SCHEMA? When should I use it?

A:INFORMATION_SCHEMA is a special database that stores MySQL’s metadata. It is very convenient for retrieving and managing information about a large number of tables, columns, indexes, permissions, and more in bulk. Since it can be queried directly with SQL, it is also useful for automation and report creation.

Q6. How can I display the results vertically?

A:By adding “G” to the end of the SQL statement and executing it, each record is displayed in a vertical list format, making it easy to understand with column names and values paired together.
Example: SELECT * FROM table_name G