August 8, 2011 at 8:49 am
I would like to pull the column names from INFORMATION_SCHEMA.COLUMNS for any one table, however I would like to store the column names into a string var. The idea being I can build a query from this (i.e. myCols = "tradeId, tradeDate, ...").
I was wondering if I could avoid a Cursor and Loop in this case ?
Here's the query which in fact works, but what's the best way to return the COLUMN_NAME column into one string var ?
select cols.COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS cols
Where cols.TABLE_NAME= 'myTradesTable'
order by cols.ORDINAL_POSITION
thanks,
Bob
August 8, 2011 at 8:53 am
select stuff((
select N', ' + cols.COLUMN_NAME as [text()]
from INFORMATION_SCHEMA.COLUMNS cols
Where cols.TABLE_NAME= 'myTradesTable'
order by cols.ORDINAL_POSITION
for xml path(''), type
).value('.','nvarchar(max)'), 1, 2, '')
August 8, 2011 at 9:00 am
And a tip: when constructing queries, you will want to investigate the quotename() function.
August 8, 2011 at 9:27 am
thanks for that response. I will give it a try .
August 8, 2011 at 10:05 am
This is quite interesting, thank you.
I saw a slightly different solution without the VALUE function:
select (
STUFF(
(select N', ' + cols.COLUMN_NAME as [text()]
from Razor_3_Snapshot.INFORMATION_SCHEMA.COLUMNS cols Where cols.TABLE_NAME= 'TradeContributionSummary'
order by cols.ORDINAL_POSITION
for xml path('')
)
,1,1,'')
)
I'm not familiar with the VALUE part...
August 8, 2011 at 11:19 am
Not only is it missing the value(), it is also missing the ", type" clause on the for xml path(''). Adding ,type makes the for xml path() clause return a xml-typed column, whereas without it, it returns some textual type (not sure which one exactly).
The problem is that even though the returned value is a textual type, it is still xml text. i.e. any '<', '>' or '&'-characters are in there as & lt ;, & gt ; and & amp ; respectively (without the spaces). The value('.','varchar(max)') call, retrieves from the root node of the xml column (the '.'-parameter) a value it is told to convert into varchar(max) (the 2nd parameter to value). The conversion into varchar(max) also implies translating the escape codes back into their original characters.
The version without the ,type and the value()-call is faster, but if the input may contain any of the said characters, these will be left as xml-escape codes in the output. Whereas the version with the ,type and the value()-call returns the correct text.
August 8, 2011 at 11:23 am
Oh okay, it's using XPATH standards. I hadn't realized before being that it was coming back in text format. It's clearer now.
Thank you again !
Bob
August 8, 2011 at 11:36 am
select (
STUFF(
(select N', ' + cols.COLUMN_NAME as [text()]
from Razor_3_Snapshot.INFORMATION_SCHEMA.COLUMNS cols Where cols.TABLE_NAME= 'TradeContributionSummary'
order by cols.ORDINAL_POSITION
for xml path('')
)
,1,1,'')
) Probably you won't notice this as a problem, but this version also leaves an additional space in front of the 1st column. This is due to an incorrect parameter to the stuff() function. The intention is to put a 2-character string ', ' in front of each column name and then to replace the first instance of this string by an empty string. As it is specified here, only the first character, the ',', is replaced by an empty string, thereby leaving a space in front of the first parameter. As said, it will probably not harm you, but just to be complete.
Again, to be complete: my suggestion to add quotename() would work out like this:
select N', ' + quotename(cols.COLUMN_NAME) as [text()]
Now, if any of the column names contain spaces or any other 'strange' characters, your generated code won't blow up on it. As it properly escapes the columns names by enclosing them in [ and ].
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply