how to print all the column names with in a table in one row from sys table without pivot

Question

Post reply

how to print all the column names with in a table in one row from sys table without pivot

vijayangk

Old Hand

Points: 382
More actions
March 2, 2012 at 3:47 pm

#269413

how to print all the column names with in a table in one row from sys table without pivot
SELECT t.name AS table_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY table_name
instead of printing in rows
Tablename Column name
Table1 column name1
Table1 column name2
Table1 column name3
Like to print as
Table1 , column name1, column name2,column name3

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

This?

SELECT OutTab.TABLE_NAME,

STUFF ( (

SELECT ', '+InTab.COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS InTab

WHERE InTab.TABLE_NAME = OutTab.TABLE_NAME

ORDER BY InTab.ORDINAL_POSITION

FOR XML PATH(''), TYPE

).value('.','VARCHAR(MAX)'

) , 1,1,SPACE(0)) AS Col_Names

FROM INFORMATION_SCHEMA.COLUMNS OutTab

GROUP BY OutTab.TABLE_NAME

vijayangk Old Hand Points: 382 More actions · Answer 2

vijayangk

Old Hand

Points: 382

March 2, 2012 at 4:03 pm

#1454405

thanks

R.P.Rozema SSChampion Points: 12317 More actions · Answer 3

ColdCoffee (3/2/2012)
This?
SELECT OutTab.TABLE_NAME,
STUFF ( (
SELECT ', '+InTab.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS InTab
WHERE InTab.TABLE_NAME = OutTab.TABLE_NAME
ORDER BY InTab.ORDINAL_POSITION
FOR XML PATH(''), TYPE
).value('.','VARCHAR(MAX)'
) , 1,1,SPACE(0)) AS Col_Names
FROM INFORMATION_SCHEMA.COLUMNS OutTab
GROUP BY OutTab.TABLE_NAME

Is there a specific reason why you put in "space(0)" and not an empty string ''?

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

R.P.Rozema (3/4/2012)
ColdCoffee (3/2/2012)
This?
SELECT OutTab.TABLE_NAME,
STUFF ( (
SELECT ', '+InTab.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS InTab
WHERE InTab.TABLE_NAME = OutTab.TABLE_NAME
ORDER BY InTab.ORDINAL_POSITION
FOR XML PATH(''), TYPE
).value('.','VARCHAR(MAX)'
) , 1,1,SPACE(0)) AS Col_Names
FROM INFORMATION_SCHEMA.COLUMNS OutTab
GROUP BY OutTab.TABLE_NAME
Is there a specific reason why you put in "space(0)" and not an empty string ''?

Just fancy stuff, no real reason 🙂

shorab99 Valued Member Points: 59 More actions · Answer 5

shorab99

Valued Member

Points: 59

March 5, 2012 at 1:10 pm

#1454980

!

vijayangk Old Hand Points: 382 More actions · Answer 6

Is it Possible to Print the column list in different column

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

vijayangk (3/6/2012)
Is it Possible to Print the column list in different column

Not sure what you are asking. Could you explain this a little better, perhaps show us what you are looking for here?

vijayangk Old Hand Points: 382 More actions · Answer 8

As of now the result has 2 columns

1st one has the table name and the second column has all the column names with comma separator

TableName | col1,col2, col3,..... |

My question: is it possible to load the each columns names in different columns

TableName | col1 |col2 | col3 |

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

vijayangk (3/7/2012)
As of now the result has 2 columns
1st one has the table name and the second column has all the column names with comma separator
TableName | col1,col2, col3,..... |
My question: is it possible to load the each columns names in different columns
TableName | col1 |col2 | col3 |

Forget what you have been given already and read the following two articles:

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Just so you know, you need to pivot the data at some point. You could use a split function on the string, but you will find that you are pivoting that data to put it in a single row.