How to improve the performance of this query?

• Jerome.J.N

  Ten Centuries

  Points: 1384

  More actions

  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

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  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]

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  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