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

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

image_1

To view a snapshot of the table columns, execute:

SELECT * FROM sale_details;

Output:-

image_2

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

image_3

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

image_4

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

image_5

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

image_6

READ MORE:

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