Post reply

How to improve the performance of this query?

  • August 6, 2013 at 1:20 am

    #278325

    The current result when take from INFORMATION_SCHEMA.COLUMNS

    TABLE COLUMN

    Table1 Column1

    Table1 Column2

    Table1 Column3

    Table2 Column1

    Table2 Column2

    Table2 Column3

    Expected result

    TABLE COLUMNS

    Table1 Column1,Column2,Column3

    Table2 Column1,Column2,Column3

    I have tried 2 methods

    Method 1

    SELECT c1.Table_name,

    STUFF((Select ',' + c2.COLUMN_NAME AS [text()]

    from INFORMATION_SCHEMA.COLUMNS c2

    where c1.TABLE_NAME=c2.TABLE_NAME and c1.column_name=c2.column_name

    --Order by c2.TABLE_NAME

    for xml path ( '' )), 1, 1,'' ) as "Column_names"

    from INFORMATION_SCHEMA.COLUMNS c1

    group by Table_name

    Which throws the error

    Msg 8120, Level 16, State 1, Line 4

    Column 'INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    But adding column_name to the group by clause does not give the desired result

    Method 2

    ;WITH CTE (SRNO,Table_name,column_name)

    AS

    (

    SELECT ROW_NUMBER() OVER (PARTITION BY Table_name ORDER BY Table_name,column_name) AS SRNO,Table_name,CAST(column_name AS VARCHAR(MAX)) FROM INFORMATION_SCHEMA.COLUMNS

    )

    ,CTE1(SRNO,Table_name,column_name)

    AS

    (

    SELECT * FROM CTE WHERE SRNO=1

    UNION ALL

    SELECT CTE.SRNO AS SRNO, CTE.Table_name,CAST (CTE1.column_name + ',' + CTE.column_name AS VARCHAR(MAX)) FROM CTE INNER JOIN CTE1

    ON CTE.Table_name=CTE1.Table_name AND CTE.SRNO=CTE1.SRNO+1

    )

    SELECT Table_name, MAX(column_name) AS CSV FROM CTE1 GROUP BY CTE1.Table_name

    option (maxrecursion 100)

    But this takes a very long time.

    Kindly help me get the desired result in the most optimum way

  • August 6, 2013 at 1:24 am

    #1638809

    Whoa.. with 542 posts, you still dont know how to post questions which will yeild best possible replies?

    Please read these links and post your question more legibly:

    Forum etiquette[/url]

    How to Post Performance Problems[/url]

  • August 6, 2013 at 1:34 am

    #1638815

    Try this:

    SELECT

    c1.Table_name

    ,STUFF(

    (

    SELECT ',' + c2.COLUMN_NAME

    FROM INFORMATION_SCHEMA.COLUMNS c2

    WHERE c1.TABLE_NAME = c2.TABLE_NAME

    FOR XML PATH ( '' ), TYPE

    ).value('.', 'varchar(max)')

    , 1, 1,'' ) AS "Column_names"

    FROM INFORMATION_SCHEMA.COLUMNS c1

    GROUP BY Table_name;

    You made a mistake in the WHERE clause in the FOR XML statement.

    By the way, ColdCoffee is right, you should learn how to post questions. It is hard to read what you are actually trying to say.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply