Get Column Names from a Table using SQL Query

In this article, we are going to learn how to get column names from a table using an SQL query. If you don't know what SQL is, keep reading because everything is covered from scratch.

SQL stands for Structured Query Language, which is used to interact with databases. Basically, queries are used to perform CRUD operations on databases.

Steps to Retrieve Column Names

Here is the step-by-step explanation of getting columns from a table.

Step 1: Check Database

First, check the database where the table exists. If the database does not exist, run the following query to create it.

CREATE DATABASE school;

To change the database, use this.

use <database_name>;

Step 2: Check Table

Ensure that the table from which you want to retrieve the column names exists. If the table does not exist, create it using the following query.

CREATE TABLE Students(
   ID INT PRIMARY KEY, 
   Name VARCHAR(50) NOT NULL,                      
   Age INT NOT NULL, 
   Class VARCHAR(20) NOT NULL,
   Grade CHAR(1) NOT NULL
);

Step 3: Insert Data into Table

You can insert data into the table using the following query.

INSERT INTO Students(ID, Name, Age, Class, Grade) VALUES
   (1, 'Aarav Sharma', 14, '9th Grade', 'A'),
   (2, 'Ishita Singh', 15, '10th Grade', 'B'),
   (3, 'Rohan Verma', 16, '11th Grade', 'C'),
   (4, 'Diya Gupta', 17, '12th Grade', 'A'),
   (5, 'Kavya Rao', 13, '8th Grade', 'B'),
   (6, 'Aditya Patel', 15, '10th Grade', 'A');

If you insert data in the same column order as defined in the table, you can omit the column names (id, name, age, class, grade) and directly provide the values.

Here is an example query to insert a single row into the table, which can also be used for multiple entries by adding more rows:

INSERT INTO students VALUES(1, 'Aarav Sharma', 14, '9th Grade', 'A');

For inserting multiple rows, use this format:

INSERT INTO students VALUES
(1, 'Aarav Sharma', 14, '9th Grade', 'A'),
(2, 'Ishita Singh', 15, '10th Grade', 'B'),
(3, 'Rohan Verma', 16, '11th Grade', 'C');

Step 4: View Table Data

To see the data inside the students' table, run the following query.

SELECT * FROM students;

Output

Step 5: Get All the column names from the table

In this step, we will retrieve all the column names from a table. Here is a query to get all column names from the students' table.

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='students';

Here is an explanation of each part of the query.

• SELECT COLUMN_NAME: This part of the query is used to retrieve the names of the columns from the specified table. Basically COLUMN_NAME is a field in the INFORMATION_SCHEMA.COLUMNS table.
• FROM INFORMATION_SCHEMA.COLUMNS: INFORMATION_SCHEMA is a system view that contains all the details of the current database. INFORMATION_SCHEMA.COLUMNS helps us to retrieve all the details of columns. It provides information such as column names, data types, and other attributes.
• WHERE TABLE_NAME = 'students': This condition filters the rows in the INFORMATION_SCHEMA.The COLUMNS table is only to return information about the columns of the student's table.

Output