How to List All Columns in MySQL

08/06/2021

Contents

In this article, you will learn how to list all columns in MySQL.

Listing all columns in MySQL

In MySQL, a database table is composed of one or more columns that store data. If you need to view a list of all the columns in a MySQL table, you can do so by querying the information_schema.columns table.

Querying the information_schema.columns table

The information_schema.columns table is a system table that contains metadata about all the columns in all the tables in a MySQL database. You can query the information_schema.columns table to view a list of all columns in a specific table.

Syntax

SELECT column_name FROM information_schema.columns WHERE table_name = 'table_name';

Example

SELECT column_name FROM information_schema.columns WHERE table_name = 'users';

Output:

+-------------+
| column_name |
+-------------+
| id          |
| username    |
| email       |
| password    |
+-------------+

This query retrieves the names of all columns in the users table by querying the information_schema.columns table. The WHERE clause limits the results to the columns in the specified table.

If you want to view all columns in all tables in a database, you can use the following query:

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';

Output:

+------------+-------------+
| table_name | column_name |
+------------+-------------+
| users      | id          |
| users      | username    |
| users      | email       |
| users      | password    |
| orders     | id          |
| orders     | user_id     |
| orders     | item_id     |
| orders     | quantity    |
+------------+-------------+

This query retrieves the names of all columns in all tables in the my_database database by querying the information_schema.columns table. The results include both the table name and column name.

#MySQL