CODEWITHSUNDEEP
sqlt-sqldynamic-sql
Anda Rădulescu
Anda Rădulescu

Reputation: 13

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!

Upvotes: 0

Views: 259

Answers (1)

Sean Pearce
Sean Pearce

Reputation: 1169

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;

Upvotes: 1

Related Questions