Anda Rădulescu Anda Rădulescu - 1 year ago 50
SQL Question

How to return all values in the first column of a table without specifying column's name?

I need to create a dynamic sql query in order to return all values in the first column of a table. The table's name needs to be a variable @tableName ( i will run this query for multiple tables depending on several conditions). Also, i don't know exactly the first column's name but it is formed out of Id_@tableName .

I need something like below but written dinamically:

select
(select column_name from INFORMATION_SCHEMA.columns where table_Name= @tableName and ordinal_position=1)
from @tableName


Could you please help me? Thank you in advance!

Answer Source
USE AdventureWorks;
GO

DECLARE @ObjectName SYSNAME = 'Sales.SalesOrderHeader';

DECLARE @SQL NVARCHAR(MAX);
SELECT
    @SQL = 'SELECT ' + QUOTENAME(name) + ' FROM ' + @ObjectName
FROM
    sys.columns
WHERE
    object_id = OBJECT_ID(@ObjectName)
    AND column_id = 1;
EXEC sp_executesql @SQL;