Categories: SQL

How To Get All Column Names Of Table In SQL

In this article, we are going to learn how to get all column names of the SQL table.

In my recent project, I want to create a store procedure in that I want a list of all column names, I can do copy-paste but the challenge is that what to do when there is more than 30-40 field it take too much time.

So I found and implement SQL queries to achieve this, let us understand it by the following example.

Syntax:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your table'
ORDER BY ORDINAL_POSITION

Example

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SalesExcelData'
ORDER BY ORDINAL_POSITION

Output

In the above figure, you can see all the columns of the SalesExcelData table.

Then after i thought that i want all these columns as a comma-separated string, For I do little modification on the above query as you can see below code.

CREATE TABLE #temp (COLUMN_NAME varchar(500))

INSERT INTO #temp
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Raw_MTDSalesData_Test'
ORDER BY ORDINAL_POSITION

DECLARE @results varchar(500)

SELECT @results = coalesce(@results + ',', '') +  convert(varchar(12),COLUMN_NAME)
FROM #temp

SELECT @results as results

In the above code first I create temp table then store all column values in it by using the above query. Then after I use coalesce() to make get column comma-separated values.

Output

In the above figure, you can see all the columns of the SalesExcelData tableĀ as separated by a comma.

Also Check, Stuff Function In SQL

Shaikh Hafeezjaha

I am Shaikh Hafeezjaha. I started my journey as a .Net Developer and Learning and developing new things in IT Industries. I completed MSC(ICT) from Veer Narmad South Gujarat University.

Recent Posts

Testing hk

Testing

2 years ago

Create and Used PIPE in angular

In this article, we have to show Create and Used PIPE in angular

2 years ago

Operation

Testing

2 years ago

Create and Used PIPE in angular

In this article, we have to show Create and Used PIPE in angular

2 years ago

Create and Used PIPE in angular

In this article, we have to show Create and Used PIPE in angular

2 years ago

TETS NEW

test

2 years ago