March 2, 2012 at 3:47 pm
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
March 2, 2012 at 3:58 pm
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
March 2, 2012 at 4:03 pm
thanks
March 4, 2012 at 12:57 am
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 ''?
March 4, 2012 at 1:02 am
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
March 5, 2012 at 1:10 pm
!
March 6, 2012 at 3:54 pm
Is it Possible to Print the column list in different column
March 6, 2012 at 6:36 pm
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?
March 7, 2012 at 7:42 am
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 |
March 7, 2012 at 7:54 am
vijayangk (3/7/2012)
As of now the result has 2 columns1st 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:
March 7, 2012 at 8:00 am
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.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy