Hi you can take use of the meta tables under the schema sys,I found one solution like:
SET @tbName = 'MyTable' SET @colName = (SELECT '''' + NAME + '&''+CONVERT(NVARCHAR(100),' + NAME + ') AS ' + NAME + ',' FROM SYS.ALL_COLUMNS WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.ALL_OBJECTS WHERE NAME = @tbName) FOR XML PATH(''))
EXEC SP_EXECUTESQL @SQL
Adding to mikedengs answer; FOR XML PATH will return special characters (&, < and so on) as encoded values (&, < and so on).
To get only the & in your result, add TYPE in your for xml path-statement like this:
See here for more info
answered May 06, 2013 at 01:07 PM
The only way I know to do something like this would to program it into the query:
Realizing you have 100 columns makes this difficult.
answered May 02, 2013 at 08:32 AM
Grant Fritchey ♦♦