MySQL get column names

This article will see how to get the column names from a table using different methods. Also, we will see how to get the comma-separated list of column names.

Table of Contents:-

• Get column names from a table using INFORMATION SCHEMA
• Get column names from a table using SHOW COLUMNS
• Get column names from a table using DESC
• MySQL get column names comma separated

Let us get started by making the sample data to be used across the examples. We will be creating a table named sale_details.

CREATE TABLE sale_details (
    id INT auto_increment,
    sale_person_name VARCHAR(255),
    no_products_sold INT,
    sales_department VARCHAR(255),
    PRIMARY KEY (id)
);

Action Output:-

To view a snapshot of the table columns, execute:

SELECT * FROM sale_details;

Output:-

Get column names from a table using INFORMATION SCHEMA

The information schema is used to get information about the MySQL server like table name, database name column names, data types, etc. Observe the below query for its usage to get the column names of a table.

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
    TABLE_SCHEMA = Database()
AND TABLE_NAME = 'sale_details' ;

Action Output Message:–

4 row(s) returned

Output:-

Output in image_3 shows the column names from the table sale_details. Database() function retrieves the current schema from the MySQL server.

READ MORE: MySQL: get column names and datatypes of a table

Get column names from a table using SHOW COLUMNS

Another way to get the column names from a table is using SHOW COLUMNS. Observe the below query for the solution.

SHOW COLUMNS FROM sale_details;

Action Output Message:–

4 row(s) returned

Output:-

Get column names from a table using DESC

We can also use the DESC keyword to get the column names. Observe the below query for the solution.

DESC sale_details;

Action Output Message:–

4 row(s) returned

Output:-

The output will be the same as with the SHOW COLUMNS statement.

MySQL get column names comma separated

If we want to view the column names in a comma-separated list, we can use the below query.

SELECT 
    CONCAT('\'',
            GROUP_CONCAT(column_name
                ORDER BY ordinal_position
                SEPARATOR '\', \''),
            '\'') AS columns
FROM
    information_schema.columns
WHERE
    table_schema = DATABASE()
        AND table_name = 'sale_details';

Action Output Message:–

1 row(s) returned

Output:-

READ MORE:

• MySQL: get column names and datatypes of a table
• MYSQL: change column order
• MySQL : Change Datatype of a Column

We hope this article helped to get the columns of a table. Good Luck !!!