JIYIK CN >

In this article, we aim to explore how to get the column names of a specific table in MySQL database.

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

MySQL helps us get this information to read, get, update, or delete any information with the correct definition and name. There are several ways to do this.

Following are the different methods that can be used to get the column names of a particular table in MySQL:

• Use DESCRIBEthe statement.
• Use SHOWthe statement.

Let us understand how each of these methods works.

Before we begin, we create a dummy dataset to work with. We create a table student_informationand 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");

DescribeUse the statement to get the column name in MySQL

MySQL provides us with DESCRIBEstatements to obtain information related to a specific table. DESCor Describecan help us understand the overall structure of the table. This information includes column names, data types, default values, column types, etc.

The basic syntax of the statement is as follows.

DESCRIBE name_of_the_table;

DESCRIBEAnother way to use the statement is as follows.

DESC name_of_the_table;

Now let us use this statement to get student_informationthe names of all the columns of the table. We can do this using the following query.

DESCRIBE student_information;

The output of the above query is 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, Defaultand Extra.

FieldIndicates student_informationthe column name of . TypeIndicates the data type of each column.

NullIndicates whether the values ​​in the column can take null values. KeyIndicates the type of the key associated with the column name.

As we can see, stu_idshows that PRIit represents the primary key of our table. Finally, we have DEFAULT, which describes any default value set for the column, while Extrastores any extra information associated with the column.

ShowUse the statement to get the column name in MySQL

ShowThe statement is Describean alternative to the statement. showThe basic syntax of the statement is as follows.

SHOW COLUMNS FROM `name_of_the_table`;

Here, name_of_the_tablerepresents the name of the table that contains the columns to be retrieved. We can get the names of all the columns showfrom the table using the statement student_informationas shown below.

SHOW COLUMNS FROM `student_information`;

The output of the above query will 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 shown in the output above, SHOWand DESCRIBEcan help us get the column names and other useful information of the table. Therefore, with the help of the above techniques, we can efficiently get the column names of the table in MySQL.