Select statement with string expression

Question

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

I know it's bad form, but why doesn't select * from X work for you? — Nathan Ernst
– Nathan Ernst, Commented Jan 4, 2011 at 15:36
actually i want to merge columns without id column. SELECT @columnNames = @columnNames + ' + ' + COLUMN_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'X' AND COLUMN_NAME <> 'id' and then i want to use select... — ogun
– ogun, Commented Jan 4, 2011 at 15:39

Martin Smith · Accepted Answer · 2011-01-04 15:36:31Z

2

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

answered Jan 4, 2011 at 15:36

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lamak · Accepted Answer · 2011-01-04 15:37:23Z

2

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.

answered Jan 4, 2011 at 15:37

Lamak

70.8k12 gold badges118 silver badges119 bronze badges

1 Comment

ogun Over a year ago

because of merge columns... thanx for your answer.

Alex K. · Accepted Answer · 2011-01-04 15:40:22Z

1

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)

answered Jan 4, 2011 at 15:40

Alex K.

177k32 gold badges275 silver badges298 bronze badges

Comments

SQLMenace · Accepted Answer · 2011-01-04 15:37:03Z

1

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

answered Jan 4, 2011 at 15:37

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Collectives™ on Stack Overflow

Select statement with string expression

4 Answers 4

Comments

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Related