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:
We hope this article helped to get the columns of a table along with their datatypes. Good Luck !!!