Getting column list into a variable

The easiest way of getting a column list into a variable is to assign the contents of syscolumns (or sys.columns for SQL 2005).

Create a variable to hold the column list and then reassign it in the select statement.
To get the column key use the object id of the object

Select @variable = @variable + coalesce(@variable + ‘,’,”) from sys.columns where object_id = object_id(‘tablename’)

We are using the fact that coalesce returns the first non null value here so that we get a ‘,’ for each of the subsequent field names but just the column name for the first field.
Much easier than using a cursor on sys.columns to get each field.

If you don’t use coalesce for the first item in the array, you will also need to initialise the variable before assigning it since null + anything = null

create table temptable(id int identity, scol1 char(30), scol2 char(30) 

Declare @myvar varchar(max);
select @myvar = coalesce(@myvar + ‘,’,”) from sys.columns where object_id = object_id(‘temptable’) order by column_id

select @myvar