tvc lists date twice

TomDTomD Posts: 4 New member
edited November 7, 2019 12:38PM in SQL Prompt
Ran the new tvc snippet on a simple table with a Birth Date [Date] column and got the following output in the @tmp:

...
Birth Date date NULL,
...

Maybe there need to be a QUOTENAME() somewhere?

Answers

  • TomDTomD Posts: 4 New member
    Yep, this seems to fix it for me:

    '+QUOTENAME(Coalesce(DetectDuplicateNames.name+'_'+Convert(VARCHAR(5),f.column_ordinal),f.name))




  • 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
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • 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 @TheExpression ;NVARCHAR(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,'')+')' 
Sign In or Register to comment.