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.
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'your table' ORDER BY ORDINAL_POSITION
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SalesExcelData' ORDER BY ORDINAL_POSITION
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.
In the above figure, you can see all the columns of the SalesExcelData table as separated by a comma.