SQL Server Performance

Convert into one Select Query

Discussion in 'SQL Server 2005 General Developer Questions' started by sonnysingh, Dec 23, 2012.

  1. sonnysingh Member

    Hi All

    I have a query that need to Rewrite it without cursor, function or loop to produce same result and should be included in ONE SELECT (no temporary object involved).

    Code:
    declare @ColName sysname,
@TableName sysname,
@FieldList varchar(MAX),
@LastTableName sysname

select @LastTableName = '',
@FieldList = ''

declare @tb table (TableName sysname null, FieldList varchar(MAX) null)
declare curs cursor for

select c.name, object_name(c.object_id)
from sys.columns c INNER JOIN sys.objects o on c.object_id = o.object_id
where o.type = 'U'
order by o.object_id

open curs
fetch curs into @ColName, @TableName
set @LastTableName = @TableName
while @@FETCH_STATUS = 0

BEGIN
if @LastTableName <> @TableName

BEGIN
insert into @tb values (@LastTableName,@FieldList)
set @FieldList = ''
set @LastTableName = @TableName
END

set @FieldList = case when @FieldList = '' then @ColName else + @FieldList + ',' + @ColName end
fetch curs into @ColName, @TableName
END

deallocate curs

insert into @tb values (@LastTableName,@FieldList)

select * from @tb
    Pls help.
    Thanks in Advance
    sonnysingh, Dec 23, 2012
    #1

  2. FrankKalis Moderator

    Try this:
    Code:
    SELECT
    O.name,
    SUBSTRING(TheColumns.TheColumn, 2, 8000) AS FieldList
FROM
    sys.objects O
    CROSS APPLY (SELECT
                    ',' + C.name
                FROM
                    sys.columns C
                WHERE
                    O.object_id = C.object_id
                ORDER BY
                    C.column_id
    FOR
                XML PATH('')) TheColumns (TheColumn)
WHERE
    O.type = 'U';
    FrankKalis, Jan 7, 2013
    #2

Share This Page