SQL Server 2008: Get first column without knowing column name

Hello Experts,

Can I get first column from a table without knowing column name in one query only without using dynamic sql?

Actually, I tried myself but could do with dynamic SQL only as below:

-- Start ===========================================
DECLARE @firstColumn NVarChar(255) -- For first column storage.
DECLARE @sqlStr NVarChar(4000) -- For get first column from a table dynamically

SELECT Top 1 @firstColumn = sc.[Name] FROM sys.columns sc
INNER JOIN sys.objects so
ON sc.[object_id] = so.[object_id]
WHERE so.[name] = 'tblTableSize' -- Pass the table name here
ORDER BY column_id ASC

SET @sqlStr = 'SELECT ' + @firstColumn + ' FROM tblTableSize' -- Also pass the table name here
--PRINT @sqlStr

EXECUTE sp_executesql @sqlStr

-- End   ===========================================

Best Regards,
Mohit Pandit
LVL 5
MohitPanditAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
here we go, but the warning is: the full table is transformed into xml, so not really a efficient method ...

declare @t table (x xml)
declare @t2 table (x xml)
declare @col_name varchar(max)

insert into @t select cast( (select * from YOUR_TABLE_NAME for xml raw) as xml)

insert into @t2
select y.r.query('.')
from @t t
cross apply t.x.nodes('/row') as y(r)

select top 1 @col_name = t2.n.value('local-name(.)', 'varchar(max)')
  from @t2 t
cross apply x.nodes('//@*') as t2(n)

--select @col_name

select t.x data
     , t2.n.value('local-name(.)', 'varchar(max)') fname
     , t2.n.value('.', 'varchar(max)') fvalue
from @t2 t
cross apply x.nodes('//@*[local-name(.)=sql:variable("@col_name")]') as t2(n)
 

Open in new window

0
 
Vijaya Reddy Pinnapa ReddyCommented:
select column_name,ordinal_position   from information_schema.columns  where table_schema = _____    and table_name = _____   and ordinal_position = 1
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>without using dynamic sql
sorry, no way without dynamic sql ...
I have some XML method in my mind, let me check something ...
0
 
MohitPanditAuthor Commented:
Thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.