(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) https://esdbp.createaforum.com --------------------------------------------------------- ***************************************************** (DIR) Return to: Scripts ***************************************************** #Post#: 30-------------------------------------------------- To list columns with specified delimiter horizontally /verticall y By: srinivasma_exceldbp Date: March 21, 2014, 4:52 am --------------------------------------------------------- -- To list columns with specified delimiter horizontally /vertically -- 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 *****************************************************