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
  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';
    

Share This Page