Get all the column names from Table


Recently, I was looking for all the column names from table and want to use them in select query. My table has total 70 column names so i thought why to type it. Why not query the syscolumns table and fetch those names.

Here is the script that will do the trick:


--Get column related information from table
SELECT * FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

--Get all the column names from Table
SELECT [name] AS [Column Name]
FROM syscolumns
WHERE id = (
		SELECT id
		FROM sysobjects
		WHERE type = 'U'
			AND [Name] = 'TableName'
		)

--Get all the columns from View
SELECT [name] AS [Column Name]
FROM syscolumns
WHERE id = (
		SELECT id
		FROM sysobjects
		WHERE type = 'V'
			AND [Name] = 'ViewName'
		)

--Get Comma separated column names from Table/View
DECLARE @ColumnList VARCHAR(max)

SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + CAST([name] AS VARCHAR(max))
FROM syscolumns
WHERE id = (
		SELECT id
		FROM sysobjects
		WHERE type = 'U'
			AND [Name] = 'TableName'
		)

SELECT @ColumnList

Hope this will help !!!

Jay Ganesh

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s