CODEWITHSUNDEEP
sqlsql-servert-sql
ogun
ogun

Reputation: 1334

Select statement with string expression

I have a table but i don't know its columns. It name is X

I can find its columns with the following code:

DECLARE @columnNames NVARCHAR(4000) = ''
SELECT @columnNames = @columnNames + ', ' + COLUMN_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'X'

Now, i want to use select statement in my table, but how?

I think, it should be like that. I didn't find correct way:

SELECT SUBSTRING(@columnNames, 2, LEN(@columnNames)) FROM X

edit:

Actually i want to merge columns without id column.

DECLARE @columnNames NVARCHAR(4000) = ''
SELECT @columnNames = @columnNames + ' + ' + COLUMN_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'X' AND COLUMN_NAME <> 'id'

And then

SELECT SUBSTRING(@columnNames, 4, LEN(@columnNames)) FROM X

Upvotes: 1

Views: 3611

Answers (4)

Alex K.
Alex K.

Reputation: 175768

If you want to select the fields in the string you fetch you would need to use EXEC (or sp_executesql)

DECLARE @columnNames NVARCHAR(4000) = ''
SELECT @columnNames = @columnNames + case when len(@columnNames)=0 then '' else ', ' end + COLUMN_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = X

DECLARE @SQL NVARCHAR(1024) = 'select ' + @columnNames + ' from X'
EXEC (@SQL)

Upvotes: 1

Lamak
Lamak

Reputation: 70638

If you are selecting all of the columns of your table, why don't just use SELECT * FROM X?. The other way is with dynamic sql and thats not a good idea.

Upvotes: 2

SQLMenace
SQLMenace

Reputation: 134941

you need to use dynamic SQL to do that, but be aware of the dangers, read this http://www.sommarskog.se/dynamic_sql.html

But since you are selecting all columns I don't see the point in what you are doing...just use SELECT * FROM X

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453028

Following the clarified goal and explanation as to why * won't work for you, you would need to concatenate the entire SELECT statement and use dynamic SQL. You should use QUOTE_NAME to avoid problems with non standard names.

See The Curse and Blessings of Dynamic SQL: Dealing with Dynamic Table and Column Names

Upvotes: 2

Related Questions