Perform SELECT Without Column Names
By Kalman Toth, M.Phil., M.Phil., MCDBA

June 19, 2005

Assume you get an external feed through DTS with changing column names. Yes it can happen! We live in a strange IT world. What you know on the other hand that you only want columns 3, 4 and 6. Since SELECT requires * or actual column name list, you have no choice but to go to sys.syscolumns system view and find out the names of those columns. Plug them into a dynamic SQL and presto... You can uncomment the SELECT prior to the dynamic execution statement to see the content of the @SQLstring variable.

use AdventureWorks
go
declare @SchemaName as sysname, @TableName as sysname
declare @Col3 as sysname, @Col4 as sysname, @Col6 as sysname
declare @SQLstring as nvarchar(512)
set @SchemaName = 'Person'
set @TableName = 'Contact'
select @Col3 = sc.name
from sys.objects as so inner join sys.syscolumns as sc on so.object_id = sc.id
where so.name = @TableName and sc.colid = 3
select @Col4 = sc.name
from sys.objects as so inner join sys.syscolumns as sc on so.object_id = sc.id
where so.name = @TableName and sc.colid = 4
select @Col6 = sc.name
from sys.objects as so inner join sys.syscolumns as sc on so.object_id = sc.id
where so.name = @TableName and sc.colid = 6
select @SQLstring = 'SELECT ' + @Col3 + ',' + @Col4 + ',' + @Col6
+ 'FROM ' + @SchemaName+'.'+@TableName

-- select @SQLstring
exec sp_executesql @SQLstring
go

Here is the partial result set:

Title FirstName LastName

Mr. Gustavo Achong
Ms. Catherine Abel
Ms. Kim Abercrombie
Sr. Humberto Acevedo
Sra. Pilar Ackerman
Ms. Frances Adams
Ms. Margaret Smith
Ms. Carla Adams