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