function to return a column separated list of column names

Question

I would like to create a function that returns a comma separated list of field name for any given table. The function should accept database, schema and table name as input as return the comma separated list.

I can do this in a stored procedure but I want to do this in a function so I can join it into datasets. However I am problems with dynamic sql is not allowed in function - so how can I do this?

here is the proc which i want to duplicate in a function

alter proc dbo.usp_generate_column_name_string
@database varchar(100),@schema varchar(100), @table varchar(100)

as

declare @str varchar(max) = '

select stuff((select '','' + name as [text()] from 
(
select c.name from ' + @database + '.sys.tables a
inner join ' + @database + '.sys.schemas b on a.schema_id = a.schema_id
inner join ' + @database + '.sys.columns c on  c.object_id= a.object_id
where b.name = '''+@schema+''' and a.name ='''+@table+''') x

for xml path ('''')),1,1,'''')


'
exec (@str)

go

exec dbo.usp_generate_column_name_string 'test' , 'dbo','jl1_tmp'

What version of SQL Server are you using? – Gordon Linoff Jan 20 '18 at 13:26 — Gordon Linoff, Jan 20 '18 at 13:26

score 0 · Accepted Answer · answered Jan 20 '18 at 13:28

There are so many ways to do it, one easier way is to insert the proc result into a temp table and use it in join

create table #coltemp(colList varchar(max))

insert into #coltemp
exec dbo.usp_generate_column_name_string 'test' , 'dbo','jl1_tmp'

select * from #coltemp

check the following question to know about diff ways to insert proc results into temp table Insert results of a stored procedure into a temporary table

score 0 · Answer 2 · answered Jan 20 '18 at 13:33

Here is the basic idea:

create function usp_generate_column_name_string (
    @schema varchar(100),
    @table varchar(100)
)
returns varchar(max) as
begin
    return (select stuff( (select ',' + column_name
                           from information_schema.columns
                           where table_name = @table and table_schema = @schema
                           for xml path ('')
                          ), 1, 1, ''
                        )
           );
end;

Notes:

This doesn't handle special characters in the column names. I'm not sure how you want to escape those, but the logic is easily adjusted.
Database is left out. That is much harder in SQL Server, because the system tables are organized by database. If that is a requirement, you basically cannot do this (easily).

function to return a column separated list of column names

2 Answers2