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

Question

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!

Bad idea. See http://stackoverflow.com/questions/4882837/access-columns-of-a-table-by-index-instead-of-name-in-sql-server-stored-procedur/4883109#4883109 — mikeagg, Jun 27 '16 at 08:25

score 1 · Accepted Answer · answered Jun 27 '16 at 08:34

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;

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

1 Answers1