SQL Server 2008: Get first column without knowing column name

Posted on 2012-08-22
Last Modified: 2012-08-22
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
Question by:MohitPandit
    LVL 9

    Expert Comment

    select column_name,ordinal_position   from information_schema.columns  where table_schema = _____    and table_name = _____   and ordinal_position = 1
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >without using dynamic sql
    sorry, no way without dynamic sql ...
    I have some XML method in my mind, let me check something ...
    LVL 142

    Accepted Solution

    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

    LVL 5

    Author Closing Comment

    Thank you very much

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now