MySQL Query to Get Column Names from Table

MySQL is a popular database used by many websites and organizations. It allows you to easily build databases for simple as well as complex schema relationships. Often you may need to get a list of all columns in a single or all tables in your MySQL database. There is an SQL query for this purpose. In this article, we will learn how to use MySQL query to get column names from table. Even when you use MySQL tools like Workbench or HeidiSQL, it is essentially this query that is fired under the hood to retrieve column names of a table.

MySQL Query to Get Column Names from Table

In MySQL, all column names, along with their table names, database names, data type and other information, are stored in database called information_schema. It is automatically created at the time of MySQL installation and is automatically updated whenever you create, modify or remove columns & tables from your system.

So you can easily get the column names in a table with the following SQL query. Log into MySQL before running the query. Replace table_name with the table name whose column names you want, and database_name with the name of your database where this table is present.

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='database_name' 
    AND `TABLE_NAME`='table_name';

Information_schema database contains many useful information such as column type, whether the column is nullable, max column size, character set, etc. If you want all that information you can do a select * instead of using select column_name.

SELECT *
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='database_name' 
    AND `TABLE_NAME`='table_name';

Alternatively, you can also use show command in MySQL to get column names.

SHOW column
from table_name;

If you want column names of all tables in your database, just omit the where clause containing table_name.

SELECT *
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='database_name';

In this article, we have learnt how to get column names from table in MySQL.

Also read:

How to Find Min & Max Values of Column in Pandas
How to Redirect Stdout & Stderr to File in Python
How to Extract Numbers from String in Python
How to Concatenate Items in List Into String in Python
How to Create Multiline String in Python