To list columns with specified delimiter horizontally /vertically

- Library Procedure to list columns with specified delimiter horizontally (one row ) or vertically ( columns)

-- Select Query -> Results to -> Text

/*
EXEC sp_lib_columns 'employee',',','H'
EXEC sp_lib_columns 'employee',',','V'
*/


CREATE procedure dbo.sp_lib_columns
@vchtablename varchar(200),
@chrdelimiter char(1) = ',',
@hvflag char(1) = 'H/V' -- Horizontal/Vertical
as

set nocount on
if @vchtablename is null or len(@vchtablename)= 0
return

if @chrdelimiter is null
return

set nocount on
declare @vchcolumns varchar(8000),@intmaxslno int
declare @tblcolumn table (Column_Name varchar(200),slno int identity(1,1))
set @vchcolumns='('
insert into @tblcolumn(Column_Name)
select column_name
from information_schema.columns where table_name=@vchtablename
order by Ordinal_position


select @intmaxslno=@@rowcount
if @hvflag = 'H'
begin
 select @vchcolumns=@vchcolumns+column_name+case when slno<>@intmaxslno then   @chrdelimiter else '' end
 from @tblcolumn
 order by slno
 select @vchcolumns + ')'

end

else

begin

 select @vchcolumns = @vchcolumns + char(10)

select @vchcolumns = @vchcolumns + column_name + case when slno<>@intmaxslno then @chrdelimiter else '' end + char(10)

 from @tblcolumn order by slno

select @vchcolumns + ')' + char(10)

end

GO