How to List MySQL Columns: Commands, Scripts & GUI Tools

1. Introduction

• When investigating the database structure
• When adding or removing columns
• When checking existing column names or data types
• When searching for tables that have specific columns
• When processing tables dynamically in code

2. How to Retrieve a Basic List of Columns

2-1. Retrieving with SHOW COLUMNS

Usage

SHOW COLUMNS FROM table_name;

SHOW COLUMNS IN table_name;

Information You Can Retrieve

Example

SHOW COLUMNS FROM users;

+-------+--------------+------+-----+---------+----------------+
| 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

• Simple and easy to use
• Allows you to quickly grasp the table structure

• Difficult to retrieve only specific columns
• Cannot filter using a WHERE clause

2-2. Retrieving with DESCRIBE

Usage

DESCRIBE table_name;

DESCRIBE users;

+-------+--------------+------+-----+---------+----------------+
| 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

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

Summary

Key Points

3. Advanced: Searching Columns with Specific Conditions

3-1. Retrieve a List of Columns for All Tables in the Database

SQL Syntax

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

Example

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

Result

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

3-2. Search for Tables Containing a Specific Column

SQL Syntax

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

Example

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

Result

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

3-3. How to Retrieve Detailed Column Information

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

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

Result

+-------------+-----------+------------+--------------+
| 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 Considerations of Using INFORMATION_SCHEMA

Benefits

Considerations

Summary

Key Points

4. Automation: Retrieve Column List with a Script

• Want to monitor database schema changes
• Want to regularly record the column list and manage schema change history
• Want to integrate with other systems and retrieve column information dynamically

4-1. Retrieve Column List with Python

Preparing the Python Script

pip install mysql-connector-python

Python Script

import mysql.connector

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

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

# Name of the table 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)

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

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

Running the Script

[users] Column List:
id
name
email
age

4-2. Retrieve Column List with a Shell Script

Preparing the Shell Script

#!/bin/bash

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

# Use MySQL command to retrieve column list
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

chmod +x mysql_columns.sh
./mysql_columns.sh

Output

column_name
id
name
email
age

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

Steps

1. Create a Python or Shell script (using the ones described above)
2. Save the output to a file
3. Set up a cron job

Example Shell Script to Save

#!/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 with MySQL command 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

Setting Up the Cron Job

crontab -e

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

Summary

Key Points

5. Error Handling: Solutions for Permission Errors

5-1. When an error occurs with SHOW COLUMNS

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

Cause of the error

Solution

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

5-2. When an error occurs with INFORMATION_SCHEMA

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

Cause of the error

Solution

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

5-3. When the root user also encounters an error

How to check privileges

SHOW GRANTS FOR 'root'@'localhost';

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

5-4. Changes to access restrictions in MySQL 8.0

Solution

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

5-5. When MySQL Workbench shows “Access denied”

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

Solution

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

Summary

Key Points

6. How to View Column Lists Using GUI Tools

• Even users who are not familiar with SQL can intuitively explore the database structure
• Quickly check a table’s column list
• Rapidly understand column data types and constraints

6-1. Viewing Column Lists with MySQL Workbench

What Is MySQL Workbench?

How to View the Column List

1. Launch MySQL Workbench
2. Connect to the 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 inspect (e.g., users)
4. Choose “Table Inspector”
5. Open the “Columns” tab

What the Column List Shows

6-2. Viewing Column Lists with phpMyAdmin

What Is phpMyAdmin?

How to View the Column List

1. Log in to phpMyAdmin
2. Select the target database from the left‑hand menu
3. Click the table whose column list you want to view
4. Open the “Structure” tab

What the Column List Shows

6-3. Alternative Tools such as DBeaver and TablePlus

DBeaver

• Cross‑platform support (Windows, macOS, Linux)
• Works with many databases beyond MySQL, including PostgreSQL, SQLite, Oracle, etc.
• Displays column lists with an intuitive GUI

TablePlus

• Simple UI that’s friendly for beginners
• Supports MySQL, PostgreSQL, SQLite, and more
• Fast performance for comfortable database management

Summary

Key Points

7. FAQ (Frequently Asked Questions)

7-1. What is the difference between SHOW COLUMNS and DESCRIBE?

Question

Answer

SHOW COLUMNS FROM users;

DESCRIBE users;

7-2. What are the benefits of using INFORMATION_SCHEMA?

Question

Answer

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

7-3. How do I find tables that contain a specific column?

Question

Answer

SELECT table_name
FROM information_schema.columns
WHERE column_name = 'column_name'
AND table_schema = 'database_name';</✅ Example execution Looking for tables that have an email column:

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

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

7-4. How can I retrieve column comment information?

Question

Answer

SHOW FULL COLUMNS FROM users;

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

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

7-5. How can I automatically retrieve column lists with a script?

Question

Answer

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("Column list:")
for column in columns:
    print(column[0])

cursor.close()
conn.close()

#!/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';"

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

Summary

Key Points

8. Summary

8-1. Comprehensive Summary of Retrieving Column Lists in MySQL

Basic Methods for Getting Column Lists

8-2. Advanced Methods for Retrieving Column Information

8-3. Automated Retrieval of Column Lists and Error Handling

Automation Methods

Error Handling

8-4. Viewing Column Lists with GUI Tools

Popular Tools

8-5. FAQ Highlights

8-6. Tips for Future Database Management

• Use SHOW CREATE TABLE to record table definitions.
• Periodically check INFORMATION_SCHEMA to track schema changes.

• Use GRANT and REVOKE to limit unnecessary rights.
• Leverage audit logs to record who modified which tables.

• Retrieve column lists with Python or Shell scripts and store them regularly.
• Use cron jobs to monitor the schema daily.

Summary and Next Steps

Next Steps