tvc lists date twice

• 
  Yep, this seems to fix it for me:
  
  '+QUOTENAME(Coalesce(DetectDuplicateNames.name+'_'+Convert(VARCHAR(5),f.column_ordinal),f.name))
  
  
  
  
  

  TomD November 07, 2019 12:44

  - 0 +
• 
  Thanks for your feedback, I have spoken with Phil Factor, the creator of that Snippet who confirms that this can be an issue in columns with illegal characters and that your workaround is a good solution. I am going to escalate this to the development team to see if we can change this.

  Sergio R November 08, 2019 16:31

  - 0 +
• 
  This should do it. Sorry about the quotename stuff. I hardly ever  seem to use columns with illegal characters in them. I meant to add something clever that added the quotes only when necessary but never got around to it.
  This should work nicely
  /**
  Summary: >
    This is a temporary batch  for creating such things as table variable,
    temporary tables or anything else that needs a column list 
  Author: Phil Factor
  **/
  Declare @TheExpressionNVARCHAR(MAX)=
  N'$SELECTEDTEXT$'
  
  SELECT 'DECLARE @$NameOfVariable$ table ('+
    Stuff ((SELECT ',
      '+QuoteName(Coalesce(DetectDuplicateNames.name+'_'+Convert(VARCHAR(5),f.column_ordinal),f.name))
       + ' '+ System_type_name + CASE WHEN is_nullable = 0 THEN ' NOT' ELSE ''END+' NULL'
     --+ CASE WHEN collation_name IS NULL THEN '' ELSE ' COLLATE '+collation_name END
     AS ThePath
    FROM sys.dm_exec_describe_first_result_set
    (@TheExpression, NULL, 1)AS f 
    -- use  sys.sp_describe_first_result_set for a batch
    LEFT OUTER JOIN 
      (SELECT name AS name FROM sys.dm_exec_describe_first_result_set
        (@TheExpression, NULL, 0) WHERE is_hidden=0 
       GROUP BY name HAVING Count(*)>1) AS DetectDuplicateNames
    ON DetectDuplicateNames.name=f.name
    WHERE f.is_hidden=0
  ORDER BY column_ordinal
  FOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,1,'')+')' 
  

  Phil_Fact0r November 08, 2019 17:35

  - 0 +
• 
  This has also been fixed in version 10.0.1.12389 of SQL Prompt

  Sergio R November 26, 2019 11:02

  - 0 +

Add comment

Please sign in to leave a comment.