Convert into one Select Query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Convert into one Select Query

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

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |