How to List Table Columns in MySQL (SHOW COLUMNS, DESCRIBE, INFORMATION_SCHEMA)

1. Introduction

When designing and managing databases with MySQL, knowing how to retrieve a list of table columns is essential.
By checking the column list, you can understand the table structure more easily, maintain data consistency, and design optimal queries.

In particular, you may need to retrieve a column list in situations like the following:

• When investigating a database structure
• When adding or deleting new columns
• When confirming existing column names or data types
• When searching for tables that contain a specific column
• When processing tables dynamically in your application

In this article, we explain MySQL column listing methods in detail, from the basics to advanced techniques.
We also include practical content such as SQL execution examples, automation scripts, and error handling, so it should be useful for a wide range of readers from beginners to intermediate users.

2. Basic Ways to List Columns

MySQL provides several ways to retrieve a list of columns. The most common methods are using SHOW COLUMNS and DESCRIBE.

2-1. Retrieve Columns with SHOW COLUMNS

The simplest way to retrieve a table’s column list in MySQL is to use the SHOW COLUMNS statement.

Usage

SHOW COLUMNS FROM table_name;

Alternatively, you can use IN instead of FROM.

SHOW COLUMNS IN table_name;

Information You Can Retrieve

When you run SHOW COLUMNS, you can retrieve information like the following.

Example

For example, to retrieve the column list for the users table, run the following SQL.

SHOW COLUMNS FROM users;

Output:

+-------+--------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)| YES  |     | NULL    |                |
| age   | int(3)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

Pros and Cons of SHOW COLUMNS

✅ Pros

• Simple and easy to use
• Helps you quickly understand the table structure

❌ Cons

• Hard to retrieve only specific columns
• You cannot filter results using a WHERE clause

2-2. Retrieve Columns with DESCRIBE

The DESCRIBE statement provides almost the same functionality as SHOW COLUMNS.

Usage

DESCRIBE table_name;

For example, to retrieve column information for the users table:

DESCRIBE users;

Output:

+-------+--------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)| YES  |     | NULL    |                |
| age   | int(3)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

Difference Between DESCRIBE and SHOW COLUMNS

DESCRIBE is an alias of SHOW COLUMNS, so the results are basically the same.
However, SHOW COLUMNS is slightly better in that it supports more detailed options.

2-3. Check Column Lists with MySQL Workbench or phpMyAdmin

You can also check a column list using GUI tools instead of the command line.

✅ In MySQL Workbench, right-click a table and select “Show Table Structure”
✅ In phpMyAdmin, click the target table and check the list under the “Structure” tab

If you are not comfortable with the CLI, GUI tools can help you work more intuitively.

Summary

In this article, we covered basic ways to list columns in MySQL, focusing on SHOW COLUMNS and DESCRIBE.

Key Takeaways

✔ SHOW COLUMNS is the most common way to list columns
✔ DESCRIBE returns almost the same result as SHOW COLUMNS
✔ You can also check column lists using GUI tools (MySQL Workbench, phpMyAdmin)

3. Advanced: Search Columns Using Conditions

SHOW COLUMNS and DESCRIBE are convenient, but when working with a large number of tables or columns,
you may want more flexible searching.
In such cases, using INFORMATION_SCHEMA is very helpful.
Here, we explain how to retrieve a list of columns across an entire database and how to find tables that contain a specific column.

3-1. Get a Column List for All Tables in a Database

MySQL provides a system view called INFORMATION_SCHEMA.COLUMNS.
By using it, you can retrieve column information for an entire database.

SQL Syntax

SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'database_name';

Example

For example, to retrieve all table names and column names in my_database:

SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'my_database';

Output

+------------+--------------+
| table_name | column_name  |
+------------+--------------+
| users      | id           |
| users      | name         |
| users      | email        |
| orders     | id           |
| orders     | user_id      |
| orders     | total_price  |
+------------+--------------+

This makes it easy to see which tables have which columns at a glance.

3-2. Find Tables That Contain a Specific Column

If you want to search only for tables that contain a specific column,
you can do that with INFORMATION_SCHEMA.COLUMNS as well.

SQL Syntax

SELECT table_name
FROM information_schema.columns
WHERE column_name = 'column_name'
AND table_schema = 'database_name';

Example

For example, to find tables that contain the email column in my_database:

SELECT table_name
FROM information_schema.columns
WHERE column_name = 'email'
AND table_schema = 'my_database';

Output

+------------+
| table_name |
+------------+
| users      |
| customers  |
+------------+

This shows that the email column exists in the users table and the customers table.

3-3. Retrieve Detailed Column Information

With INFORMATION_SCHEMA.COLUMNS, you can also retrieve details such as data type, NULL allowance, and default values.

SQL Syntax

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'table_name'
AND table_schema = 'database_name';

Example

For example, to retrieve detailed information for each column in the users table:

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users'
AND table_schema = 'my_database';

Output

+-------------+-----------+------------+--------------+
| column_name | data_type | is_nullable | column_default |
+-------------+-----------+------------+--------------+
| id          | int       | NO         | NULL         |
| name        | varchar   | YES        | NULL         |
| email       | varchar   | YES        | NULL         |
| age         | int       | YES        | NULL         |
+-------------+-----------+------------+--------------+

3-4. Benefits and Notes When Using INFORMATION_SCHEMA

Benefits

✅ You can retrieve column information for an entire database (with SHOW COLUMNS, you can only retrieve one table at a time)
✅ You can freely filter using SQL queries (you can use a WHERE clause to retrieve only specific columns)
✅ You can also JOIN and combine with other information

Notes

⚠ On large databases, queries may become slow
⚠ If you do not specify table_schema for the target database, unnecessary information may be included

Summary

In this section, we used INFORMATION_SCHEMA.COLUMNS to explain
how to retrieve a column list across an entire database and how to find tables that contain a specific column.

Key Takeaways

✔ With INFORMATION_SCHEMA.COLUMNS, you can search for specific columns rather than a full table
✔ Compared to SHOW COLUMNS, it enables free filtering with SQL queries
✔ You can also retrieve detailed column info (data type, NULL allowance, default values, etc.)
✔ For large databases, you need to pay attention to performance

4. Automation: Retrieve Column Lists with Scripts

You can manually retrieve column lists using SHOW COLUMNS or INFORMATION_SCHEMA, but
you may feel that running SQL every time is inconvenient.

In particular, automated retrieval of column lists is useful in cases like:

• When you want to monitor changes to a database structure
• When you want to record column lists periodically to manage schema change history
• When you want to integrate with other systems and retrieve column information dynamically

This section explains how to automatically retrieve MySQL column lists using Python or Shell scripts.

4-1. Retrieve Column Lists with Python

Python provides a library called mysql-connector-python. Using it, you can connect to MySQL and retrieve column lists.

Prepare the Python Script

If the library is not installed, install it with the following command.

pip install mysql-connector-python

Python Script

Next, create the following script.
This script retrieves and prints the column list for a specific table.

import mysql.connector

# Configure MySQL connection settings
config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': 'localhost',
    'database': 'your_database'
}

# Connect to MySQL
conn = mysql.connector.connect(**config)
cursor = conn.cursor()

# Table name to retrieve
table_name = "users"

# Retrieve column list
query = f"SELECT column_name FROM information_schema.columns WHERE table_name = '{table_name}' AND table_schema = '{config['database']}'"
cursor.execute(query)

# Print results
columns = cursor.fetchall()
print(f"Column list for [{table_name}]:")
for column in columns:
    print(column[0])

# Close connection
cursor.close()
conn.close()

Run the Script

When you run the script, it prints a column list like the following.

Column list for [users]:
id
name
email
age

4-2. Retrieve Column Lists with a Shell Script

You can also retrieve a column list with a Shell script (Bash) without using Python.
In Linux environments and server administration, this can be a quick and convenient option.

Prepare the Shell Script

Create a script like the following and save it as mysql_columns.sh.

#!/bin/bash

DB_NAME="your_database"
TABLE_NAME="users"
USER="your_user"
PASSWORD="your_password"

# Retrieve column list using the MySQL command
mysql -u$USER -p$PASSWORD -D$DB_NAME -e "SELECT column_name FROM information_schema.columns WHERE table_name = '$TABLE_NAME' AND table_schema = '$DB_NAME';"

How to Run

Grant execute permission and run it.

chmod +x mysql_columns.sh
./mysql_columns.sh

Output

column_name
id
name
email
age

This method lets you quickly retrieve column lists on a Linux server.

4-3. Periodically Retrieve and Save Column Lists (Cron Job)

Instead of running scripts manually, you can also retrieve column lists periodically and save them to a file.
For example, you can log the column list once per day to track schema changes.

Steps

1. Create a Python script or Shell script (use the ones above)
2. Save the output to a file
3. Configure a cron job

Example Shell Script That Saves Output

#!/bin/bash

DB_NAME="your_database"
TABLE_NAME="users"
USER="your_user"
PASSWORD="your_password"
OUTPUT_FILE="/path/to/logs/${TABLE_NAME}_columns_$(date +\%Y\%m\%d).txt"

# Retrieve column list and save to a file
mysql -u$USER -p$PASSWORD -D$DB_NAME -e "SELECT column_name FROM information_schema.columns WHERE table_name = '$TABLE_NAME' AND table_schema = '$DB_NAME';" > $OUTPUT_FILE

Configure the cron job

Run the following command to open the cron configuration.

crontab -e

Then add the following line. (Runs every day at 3:00 AM.)

0 3 * * * /path/to/mysql_columns.sh

This enables automation that retrieves column lists every day and saves them to a file.

Summary

In this section, we explained how to automatically retrieve MySQL column lists with scripts.

Key Takeaways

✔ You can retrieve column lists with a Python script (using MySQL Connector)
✔ You can also retrieve them with a Shell script using the MySQL command
✔ You can use cron jobs to log column lists periodically
✔ This is useful for tracking database schema change history

In the next article, we explain “How to fix errors when they occur.”
If you want to learn about permission errors and how to fix them when running SHOW COLUMNS or INFORMATION_SCHEMA, be sure to check the next section as well!

5. Error Handling: How to Fix Permission Errors

When retrieving column lists with SHOW COLUMNS or INFORMATION_SCHEMA in MySQL,
you may encounter errors such as “permission denied”.

This section explains common causes and solutions.

5-1. If You Get an Error with SHOW COLUMNS

When you run SHOW COLUMNS, you may see an error like the following.

ERROR 1142 (42000): SELECT command denied to user 'user'@'localhost' for table 'users'

Cause

This error occurs because the user does not have the SELECT privilege on the table.
SHOW COLUMNS requires the SELECT privilege.

Solution

Log in as an administrator (root user) and grant the SELECT privilege to the target user.

GRANT SELECT ON your_database.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

After that, SHOW COLUMNS should work.

5-2. If You Get an Error with INFORMATION_SCHEMA

When you run a query using INFORMATION_SCHEMA, you may see an error like the following.

ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

Cause

To query INFORMATION_SCHEMA, you may need not only SELECT privilege but also permission to access INFORMATION_SCHEMA.

Solution

Grant access to INFORMATION_SCHEMA with the following commands.

GRANT SELECT ON information_schema.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

This enables access to INFORMATION_SCHEMA.COLUMNS.

5-3. If You Get Errors Even as the root User

In some environments, even the root user may have restricted access to SHOW COLUMNS or INFORMATION_SCHEMA.
In that case, check the privileges.

How to Check Privileges

SHOW GRANTS FOR 'root'@'localhost';

If ALL PRIVILEGES is not granted, you can fix it with:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

5-4. Permission Changes in MySQL 8.0

In MySQL 8.0, some default privilege settings have changed.
In particular, access to INFORMATION_SCHEMA may be restricted by default, which can cause errors.

Solution

In MySQL 8.0, you may need to grant SELECT on the mysql database.

GRANT SELECT ON mysql.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

This can allow INFORMATION_SCHEMA queries to work in MySQL 8.0 as well.

5-5. If MySQL Workbench Shows “Access denied”

When running SHOW COLUMNS in MySQL Workbench, you may see an error message like:

Error: Access denied; you need (at least one of) the SELECT privilege(s) for this operation

Solution

In MySQL Workbench, you can also grant privileges through the GUI.

1. Open “Administration” → “Users and Privileges”
2. Select the target user
3. In the “Schema Privileges” tab, grant the SELECT privilege
4. Click “Apply” to save

This should allow MySQL Workbench to retrieve column lists successfully.

Summary

In this section, we explained how to troubleshoot errors when running SHOW COLUMNS and INFORMATION_SCHEMA in MySQL.

Key Takeaways

✔ SHOW COLUMNS errors are typically caused by missing SELECT privileges → fix with GRANT SELECT
✔ INFORMATION_SCHEMA errors are typically caused by missing access to information_schema.* → fix with GRANT SELECT ON information_schema.*
✔ In MySQL 8.0, you may also need privileges on mysql.*
✔ MySQL Workbench privilege errors can be fixed via the GUI

6. How to Check Column Lists Using GUI Tools

So far, we have introduced ways to retrieve column lists using SQL commands, but
with GUI (Graphical User Interface) tools, you can visually confirm columns.

GUI tools are especially useful in situations like:

• When users who are not familiar with SQL want to check database structure intuitively
• When you want to quickly check a table’s column list
• When you want to quickly understand column data types and constraints

This section explains how to check column lists using 대표적인 GUI tools such as MySQL Workbench and phpMyAdmin.

6-1. Check Column Lists in MySQL Workbench

What is MySQL Workbench?

MySQL Workbench is MySQL’s official database management tool, and it is a convenient tool that lets you visually manage tables and column information via a GUI.

Steps to Check a Column List

1. Launch MySQL Workbench
2. Connect to your MySQL server

• Select “Local Instance MySQL” or the connection you configured

1. Open the “Schemas” tab
2. Expand the target database (e.g., my_database)
3. Right-click the table you want to check (e.g., users)
4. Select “Table Inspector”
5. Open the “Columns” tab

What You See in the Column List

In the “Columns” tab, you will see information like the following.

✅ You can check column details intuitively without running SQL
✅ You can also check indexes and constraints along with the table

6-2. Check Column Lists in phpMyAdmin

What is phpMyAdmin?

phpMyAdmin is a tool that lets you manage MySQL in a web browser.
It is often provided by default on shared hosting and similar environments, so it is easy to use.

Steps to Check a Column List

1. Log in to phpMyAdmin
2. Select the target database from the left menu
3. Click the table you want to inspect
4. Open the “Structure” tab

What You See in the Column List

In the “Structure” tab, the table’s column information is displayed in a table format.

✅ You can easily check column lists in a web browser
✅ You can add/edit/delete columns via the GUI

6-3. Alternative Tools: DBeaver and TablePlus

Besides MySQL Workbench and phpMyAdmin, there are other useful database management tools.

DBeaver

• Cross-platform (Windows, Mac, Linux)
• Supports many databases besides MySQL, such as PostgreSQL, SQLite, and Oracle
• Lets you display column lists with an intuitive GUI

TablePlus

• Simple UI that is easy for beginners
• Supports MySQL, PostgreSQL, SQLite, and more
• Fast performance for comfortable database management

✅ With these tools, checking column lists and operating on data becomes even smoother

Summary

In this section, we explained how to check column lists using GUI tools.

Key Takeaways

✔ In MySQL Workbench, you can check columns in “Table Inspector” → “Columns”
✔ In phpMyAdmin, you can view column info from the “Structure” tab
✔ Alternative tools such as DBeaver and TablePlus are also useful
✔ GUI tools let you confirm column information intuitively without SQL knowledge

7. FAQ (Frequently Asked Questions)

Here are common questions readers have about listing MySQL columns.
We also explain caveats when using SHOW COLUMNS and INFORMATION_SCHEMA, how to deal with errors, and advanced usage.

7-1. What’s the Difference Between SHOW COLUMNS and DESCRIBE?

Question

“What is the difference between SHOW COLUMNS and DESCRIBE?”

Answer

SHOW COLUMNS and DESCRIBE provide almost the same functionality.
In fact, DESCRIBE is an alias of SHOW COLUMNS.

✅ SHOW COLUMNS example

SHOW COLUMNS FROM users;

✅ DESCRIBE example

DESCRIBE users;

The differences are mainly:

For most use cases, DESCRIBE is fine,
but if you need more flexible options, choose SHOW COLUMNS.

7-2. What Are the Benefits of Using INFORMATION_SCHEMA?

Question

“What are the benefits of using INFORMATION_SCHEMA instead of SHOW COLUMNS?”

Answer

Using INFORMATION_SCHEMA.COLUMNS enables advanced searching such as:

✅ Retrieve column information across an entire database
✅ Find tables that contain a specific column
✅ Filter results using SQL WHERE clauses

For example, when you want to “find all tables that have an email column,”
SHOW COLUMNS cannot do this directly, but INFORMATION_SCHEMA can.

SELECT table_name
FROM information_schema.columns
WHERE column_name = 'email'
AND table_schema = 'my_database';

SHOW COLUMNS can only retrieve information one table at a time,
but INFORMATION_SCHEMA can search across the entire database.

7-3. How Do I List Tables That Contain a Specific Column?

Question

“Is there a way to list tables that contain a specific column in a database?”

Answer

You can use INFORMATION_SCHEMA.COLUMNS to find tables that contain a specific column.

✅ SQL syntax

SELECT table_name
FROM information_schema.columns
WHERE column_name = 'column_name'
AND table_schema = 'database_name';

✅ Example
“I want to find tables that contain an email column.”

SELECT table_name
FROM information_schema.columns
WHERE column_name = 'email'
AND table_schema = 'my_database';

✅ Output

+------------+
| table_name |
+------------+
| users      |
| customers  |
+------------+

This method helps you quickly confirm which tables contain a specific column in your database.

7-4. How Can I Retrieve Column Comments?

Question

“How can I retrieve comment information set on columns?”

Answer

In MySQL, you can set comments (descriptions) on columns.
To retrieve comments, use SHOW FULL COLUMNS or INFORMATION_SCHEMA.COLUMNS.

✅ SHOW FULL COLUMNS example

SHOW FULL COLUMNS FROM users;

✅ Using INFORMATION_SCHEMA

SELECT column_name, column_comment
FROM information_schema.columns
WHERE table_name = 'users'
AND table_schema = 'my_database';

✅ Output

+-------------+---------------------+
| column_name | column_comment      |
+-------------+---------------------+
| id          | User ID             |
| name        | User name           |
| email       | Email address       |
| age         | Age (optional)      |
+-------------+---------------------+

7-5. How Can I Automate Retrieving Column Lists with Scripts?

Question

“Is there a way to retrieve column lists with a script and log them automatically?”

Answer

With Python or Shell scripts, you can retrieve and save column lists periodically.

✅ Python script example

import mysql.connector

config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': 'localhost',
    'database': 'your_database'
}

conn = mysql.connector.connect(**config)
cursor = conn.cursor()

cursor.execute("SELECT column_name FROM information_schema.columns WHERE table_name = 'users'")
columns = cursor.fetchall()

print("Columns:")
for column in columns:
    print(column[0])

cursor.close()
conn.close()

✅ Shell script example

#!/bin/bash

DB_NAME="your_database"
TABLE_NAME="users"
USER="your_user"
PASSWORD="your_password"

mysql -u$USER -p$PASSWORD -D$DB_NAME -e "SELECT column_name FROM information_schema.columns WHERE table_name = '$TABLE_NAME';"

✅ Scheduled run (cron job)

0 3 * * * /path/to/mysql_columns.sh

(Retrieves and logs the column list every day at 3:00 AM.)

Summary

In this section, we introduced frequently asked questions and solutions about listing MySQL columns.

Key Takeaways

✔ SHOW COLUMNS and DESCRIBE are similar, but SHOW COLUMNS is more flexible
✔ INFORMATION_SCHEMA lets you search columns across the database
✔ Use INFORMATION_SCHEMA.COLUMNS to find tables that contain a specific column
✔ Use SHOW FULL COLUMNS or INFORMATION_SCHEMA to retrieve column comments
✔ Python or Shell scripts can automate column retrieval and cron can schedule it

8. Final Summary

In this article, we explained how to retrieve a list of columns in MySQL in detail.
From basic SQL commands to advanced search methods, automation, error handling, and GUI tool usage,
we covered a wide range of practical knowledge.
Finally, let’s recap the most important points from the entire article.

8-1. Complete Overview: How to List Columns in MySQL

Basic Methods

✅ SHOW COLUMNS and DESCRIBE are simple and convenient
✅ INFORMATION_SCHEMA lets you search columns across the database

8-2. Advanced Ways to Retrieve Column Information

✅ You can search for tables that contain a specific column (useful during schema changes)
✅ You can also retrieve data type and NULL allowance information

8-3. Automation and Error Handling

Automation Methods

✅ Scripts can automate column listing
✅ Cron jobs enable periodic schema monitoring

Error Handling

✅ Privilege-related errors can be resolved with the GRANT command
✅ Using INFORMATION_SCHEMA may require special privileges

8-4. Check Column Lists Using GUI Tools

Common Tools

✅ GUI tools let you check columns without SQL commands
✅ Database design and editing can be done more intuitively

8-5. FAQ Highlights

✅ Provide clear answers to common questions
✅ Use concrete SQL examples to improve practicality

8-6. Tips for More Efficient Database Management

Finally, here are some tips to improve database management efficiency.

✅ Document your table structure

• Use SHOW CREATE TABLE to record table structures
• Check INFORMATION_SCHEMA periodically to track schema changes

✅ Set up proper privilege management

• Use GRANT and REVOKE to limit unnecessary privileges
• Use audit logs to record who changed which tables

✅ Automate routine tasks with scripts

• Retrieve and log column lists periodically using Python or Shell scripts
• Use cron jobs to monitor schema changes daily

Summary and Next Steps

In this article, we explained how to list columns in MySQL systematically from basics to advanced.
In database management and development, listing columns is essential for understanding table structures and improving debugging efficiency.

Next Steps

✅ Try listing columns in MySQL in your environment
✅ Try automating the workflow with Python or Shell scripts
✅ Use GUI tools to streamline database management

With these skills, you can manage MySQL databases more efficiently! 🚀