Mnishar (7/4/2013)

Check this out

http://www.sqlblogspot.com/2013/04/convert-rows-into-comma-separated.html%5B/quote%5D

Hi Nnishar,

Consider using GROUP BY instead of DISTINCT. Here's your code with the WHERE clause removed to try to get the rowcounts up a bit. The first section uses DISTINCT and the second uses GROUP BY.

PRINT '========== DISTINCT ====================================================';

SET STATISTICS TIME,IO ON

SELECT Distinct col2.table_name,

Stuff((SELECT ',' + column_name

-- Stuff used here only to strip the first character which is comma (,).

FROM information_schema.columns col1

WHERE col1.table_name = col2.table_name

FOR xml path ('')), 1, 1, '')

FROM information_schema.columns col2

SET STATISTICS TIME,IO OFF

GO

PRINT '========== GROUP BY ====================================================';

SET STATISTICS TIME,IO ON

SELECT col2.table_name,

Stuff((SELECT ',' + column_name

-- Stuff used here only to strip the first character which is comma (,).

FROM information_schema.columns col1

WHERE col1.table_name = col2.table_name

FOR xml path ('')), 1, 1, '')

FROM information_schema.columns col2

group by table_name

SET STATISTICS TIME,IO OFF

Here are the results from the "Messages" tab... you'll see the differences immediately.

========== DISTINCT ====================================================

(30 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syscolpars'. Scan count 31, logical reads 77, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysschobjs'. Scan count 31, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 62 ms, elapsed time = 134 ms.

========== GROUP BY ====================================================

(30 row(s) affected)

Table 'syscolpars'. Scan count 31, logical reads 77, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysschobjs'. Scan count 31, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 69 ms.

The other thing to worry about is that XML will entitize (change to a code sequence) certain characters if they are present. It likely won't matter for the OP's problem but it certainly could for other applications of the technique. Here's one way to overcome that problem using TYPE along with the VALUE conversion.

PRINT '========== GROUP BY DE-ENTITIZED =======================================';

SET STATISTICS TIME,IO ON

SELECT col2.table_name,

Stuff((SELECT ',' + column_name

-- Stuff used here only to strip the first character which is comma (,).

FROM information_schema.columns col1

WHERE col1.table_name = col2.table_name

FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'')

FROM information_schema.columns col2

group by table_name

SET STATISTICS TIME,IO OFF

Unfortunately, the de-entitization process causes the code to use a fair bit more CPU (although it still beats DISTINCT by a fairly wide margin). For small stuff, that certainly won't seem like it matters but it will definitely matter on bigger stuff.

========== GROUP BY DE-ENTITIZED =======================================

(30 row(s) affected)

Table 'syscolpars'. Scan count 31, logical reads 77, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysschobjs'. Scan count 31, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 18 ms.