How to Get Column Names in MySQL

1. Use the Describe Statement to Get Column Names in MySQL
2. Use the Show Statement to Get Column Names in MySQL

In this tutorial, we aim at exploring how to fetch the column names of a particular table in a MySQL database.

Generally, while working with data in MySQL, we tend to forget the names of the column and the data types of different columns for a particular table of a database.

MySQL helps us fetch this information to read, fetch, update, or delete any information with the correct definitions and names at hand. There are several methods to get this job done.

The following are the different methods that can be used for getting the column names of a particular table in MySQL:

• Using the DESCRIBE statement.
• Using the SHOW statement.

Let us understand how each one of these methods works.

Before we begin, we create a dummy dataset to work on. We create a table, student_information, along with a few rows.

-- create the table student_information
CREATE TABLE student_information(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_information
INSERT INTO student_information(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

Use the Describe Statement to Get Column Names in MySQL

MySQL provides us with the DESCRIBE statement to get information related to a particular table. DESC or Describe both help us understand the overall structure of the table. This information includes column names, data types, default values, column types, and so on.

The basic syntax of this statement can be illustrated as follows.

DESCRIBE name_of_the_table;

Another way of using the DESCRIBE statement is as follows.

DESC name_of_the_table;

Now let us use this statement to get the names of all the columns of the student_information table. We can perform this operation with the following query.

DESCRIBE student_information;

The output of the query above would be as follows.

Field			Type			Null		Key		Default		Extra
stu_id			float			NO			PRI		-			-
stu_firstName	varchar(255)	YES			-		-			-
stu_lastName	varchar(255)	YES			-		-			-

As we can see above, we have Field, Type, Null, Key, Default, and Extra.

Field indicates the names of the column of the student_information. Type indicates the data type of each of the columns.

Null indicates whether the values in the column can acquire a null value. Key indicates the type of key associated with the column name.

As we can see, stu_id shows PRI, which represents the primary key of our table. Lastly, we have DEFAULT which states any default value set for the columns, and Extra stores any extra information associated with the column.

Use the Show Statement to Get Column Names in MySQL

The Show statement is an alternative to the Describe statement. The basic syntax of the show statement is as follows.

SHOW COLUMNS FROM `name_of_the_table`;

Here, name_of_the_table represents the table name containing the columns fetched. We can get the names of all columns from the student_information table using the show statement as follows.

SHOW COLUMNS FROM `student_information`;

The output of the query above would give us the following result.

Field			Type			Null		Key		Default		Extra
stu_id			float			NO			PRI		-			-
stu_firstName	varchar(255)	YES			-		-			-
stu_lastName	varchar(255)	YES			-		-			-

As the output above indicates, SHOW and DESCRIBE can help us fetch the names of the columns of a table and other useful information. Thus, with the help of the techniques mentioned above, we can efficiently get the column names of a table in MySQL.