INFORMATION_SCHEMA.Columns manipulation 2

[dswitzer]

Here is an example of the INFORMATION_SCHEMA.Columns table:

table_catalog table_schema table_name column_name
db1 dbo tblName1 colName1
db1 dbo tblName1 colName2
db1 dbo tblName2 colName1
db1 dbo tblName2 colName2
db1 dbo tblName2 colName3

the result I want (in a select statement) is:
table_name combined_columns
tblName1 colName1,colName2
tblName2 colName1,colName2,colName3

Now, I can do this on an individual level, setting a variable for each table:

use pubs
declare @columnlist as varchar(8000)
declare @tblName as varchar(20)
set @tblName='titles'

SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name 
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = @tblName
--and columnproperty(object_id(@tblName), column_name,'IsIdentity') <> 1
print @ColumnList
[\code]

This returns:
title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate

But that is not what I want. I want:
tbl_name  column_list
titles    title_id, title, type, pub_id, price, etc....
stores    stor_id, stor_name, stor_address, city, state, zip

Ideas?

Thanks.

 

[gmmastros]

First, create this user defined function.

Create Function GetColumnList
	(@TblName VarChar(200))
Returns VarChar(8000)
AS
Begin
Declare @ColumnList VarChar(8000)

SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name 
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = @tblName
Order By Column_Name

Return @ColumnList

End

Then, you can get your required output with this...

Select Table_Name, 
       dbo.GetColumnList(Table_Name) As ColumnList
From   Information_Schema.Tables 
Where  Table_Type='Base Table'
Order By Table_Name

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

you probably want to change
Order By Column_Name

to
Order By ordinal_position

if you want the column names in the same order as in the table

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[dswitzer]

Excellent - that is what I was looking for! Thanks George and Denis.

For future searchers: if you want to exclude columns that are identity columns, add this into the where statement of the udf:

and columnproperty(object_id(Table_Name), column_name,'IsIdentity') <> 1

Thanks again.