MySQL: get column names and datatypes of a table

This article will be looking into how to get the column names and their corresponding data types in a MySQL table.

Table of Contents

• Get column names and datatypes – using information schema
• Get column names and datatypes – using SHOW COLUMNS

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

CREATE TABLE students_data (
student_id INT AUTO_INCREMENT,
student_name VARCHAR(255),
student_address VARCHAR(255),
student_grade VARCHAR(50),
student_subject VARCHAR(255),
 PRIMARY KEY (student_id)
);

Let us see if the table got created by executing :

SELECT * FROM students_data;

Output:-

The table is blank as we have not inserted any rows into it.

Get column names and datatypes – using information schema

We can use INFORMATION_SCHEMA to get the necessary information, such as a column’s names in a table. We can get the database metadata and other information like tables, columns, and access privileges from the INFORMATION_SCHEMA.

Observe the below query to get the column names from a MySQL table and their corresponding datatypes.

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

Action Output Message Response:–

5 row(s) returned

Output:-

Output in figure 2 shows the column names and their corresponding data types from the table students_data. Database() function retrieves the current schema from the MySQL server.

Alternatively, if we want to see all the other information as well around the columns’ of the table students_data, we can execute the below query.

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
    TABLE_SCHEMA = Database()
AND TABLE_NAME = 'students_data' ;

Action Output Message Response:–

5 row(s) returned

Output:-

Output in figure 3 shows that a lot of other information about columns can be retrieved, like ordinal_position, is_nullable, numeric_precision, etc.

Get column names and datatypes – using SHOW COLUMNS

Another simple way to get the columns of a table is using SHOW COLUMNS statements. Let us see how to use the same in the below query.

SHOW COLUMNS FROM students_data;

Action Output Message Response:–

5 row(s) returned

Output:-

First, two columns in the output (figure 4) show the column name and their datatypes from the students_data table.

Alternatively, if we want to see some more information on the columns for the table students_data, we can use the FULL keyword in the query. Observe the below query.

SHOW FULL COLUMNS FROM students_data;

Action Output Message Response:–

5 row(s) returned

Output:-

Using FULL keyword along with SHOW COLUMNS will retrieve some more information about the columns, for example, privileges as shown in the output (figure 5).

READ MORE:

• MySQL get column names

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