Iterate through temporary table columns to select them

Question

I have a final temporary table (#tempTable) with unknown columns number. My final select is like this, it works :

SELECT temp.* FROM #tempTable temp

But instead of a '*' I would like to call each columns individually :

SELECT temp.col1, temp.col2 FROM #tempTable temp

To do so I need to iterate through my columns names and create a procedure, I tried something like this :

DECLARE @ColName VARCHAR(255)
SELECT @ColName = min(name) FROM   tempdb.sys.columns
            WHERE  object_id = Object_id('tempdb..#TEMPTABLE'); 
WHILE @ColName is not null
BEGIN
-- i need to do it all in once and not each time....
declare @sql varchar(max) = 'SELECT tp.'+'@COlName'+'FROM #TEMPTABLE tp'
exec(@sql)
-- Increment the value, how to go to next column ?
select @ColName = min(name) FROM tempdb.sys.columns WHERE  object_id = 
Object_id('tempdb..#TEMPTABLE') > @ColName -- does not work because it is a string (column name)
END

Answer 1

Try this:

DECLARE @ColName VARCHAR(2000) = 'select '
SELECT @ColName = @ColName + ' temp.' + name + ',' FROM tempdb.sys.columns
            WHERE  object_id = Object_id('tempdb..#TEMPTABLE')
--delete last character, which is comma and append table name
@ColName = substring(@ColName, 1, LEN(@ColName) - 1) + ' from #TEMPTABLE temp'

exec(@ColName)

This query construct whole table list combined in select ... from ... statement. I increased size of the varchar variable, so it can accomodate long queries.

Also, IMO variable name such as @sql or @query would be more meaningful.