HOW TO execute a SELECT query Without Column Names
A developer posted an unusual T-SQL question on an online Message Board I frequent. He wanted to know how to find a column's value without specifying the column name. I guessed syscolumns would be part of the puzzle and rather than construct the query I searched & found a complete working snippet by Kalman Toth. It gets the third, fourth and sixth columns of the Contact table (in SQL Server 2005's AdventureWorks database) without explicitly specifying the column names in the SELECT query -
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
This goes on to show how sharing discoveries by using the right keywords can help fellow developers. Choosing unambiguous names & titles helps search engines to rank relevant links higher.
On a related note, I remember in the early years of C#, Google misunderstanding the context (like spell-checkers do) would exclude the "#" part possibly as part of sanitizing input
Also see:
HOW TO merge two HTML tables with Javascript
Can VS 2008 editions be installed side-by-side?
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
This goes on to show how sharing discoveries by using the right keywords can help fellow developers. Choosing unambiguous names & titles helps search engines to rank relevant links higher.
On a related note, I remember in the early years of C#, Google misunderstanding the context (like spell-checkers do) would exclude the "#" part possibly as part of sanitizing input
Also see:
HOW TO merge two HTML tables with Javascript
Can VS 2008 editions be installed side-by-side?
Comments
Post a Comment