Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
tsql:list_columns_in_a_temporary_table [2019/05/15 14:57]
dyson
tsql:list_columns_in_a_temporary_table [2019/05/21 16:58] (current)
dyson
Line 2: Line 2:
  
 <code tsql>​DECLARE @tempTable NVARCHAR(128) = '#​tmp'​ <code tsql>​DECLARE @tempTable NVARCHAR(128) = '#​tmp'​
 + 
 ;WITH cols AS ( ;WITH cols AS (
   SELECT   SELECT
Line 9: Line 9:
   , UPPER(c.DATA_TYPE) AS DATA_TYPE   , UPPER(c.DATA_TYPE) AS DATA_TYPE
   , CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) AS CHARACTER_MAXIMUM_LENGTH   , CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) AS CHARACTER_MAXIMUM_LENGTH
 +  , CAST(c.CHARACTER_OCTET_LENGTH AS VARCHAR) AS CHARACTER_OCTET_LENGTH
   FROM tempdb.sys.objects o   FROM tempdb.sys.objects o
     INNER JOIN tempdb.sys.schemas s ON s.[schema_id] = o.[schema_id]     INNER JOIN tempdb.sys.schemas s ON s.[schema_id] = o.[schema_id]
Line 19: Line 20:
   cols.ColumnName   cols.ColumnName
 , ', ' + cols.ColumnName + ' ' + cols.DATA_TYPE , ', ' + cols.ColumnName + ' ' + cols.DATA_TYPE
-    + IIF(cols.DATA_TYPE LIKE '​%CHAR'​'​('​ + cols.CHARACTER_MAXIMUM_LENGTH + '​)'​''​)+    + CASE 
 +        WHEN cols.DATA_TYPE LIKE '​%CHAR' ​THEN '​('​ + cols.CHARACTER_MAXIMUM_LENGTH + '​)'​ 
 +        WHEN cols.DATA_TYPE LIKE '%BINARY'​ THEN '​('​ + cols.CHARACTER_OCTET_LENGTH + ')
 +        ELSE ''​ 
 +      END 
 , ', ' + LEFT(cols.ColumnName + REPLICATE('​ ', ml.[MaxLength]),​ ml.[MaxLength]) ​ , ', ' + LEFT(cols.ColumnName + REPLICATE('​ ', ml.[MaxLength]),​ ml.[MaxLength]) ​
     + ' ​ -- ' + UPPER(cols.DATA_TYPE)     + ' ​ -- ' + UPPER(cols.DATA_TYPE)