title: Dynamic SQL to iterate over all columns based on a complex Static SQL
body: <p>I have a static SQL query which finds out blank counts and data types (whether numeric or alphanumeric etc). Below is the query:</p>
  
  <pre><code>SELECT
  case when Pattern = '' then 'BLANK' else Pattern end AS Pattern,
  LEN(case when Pattern = '' then 'BLANK' else Pattern end) Length,
  COUNT(*) AS Count FROM
       ( SELECT REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(      
       REPLACE( REPLACE( REPLACE( REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(REPLACE( REPLACE( 
       REPLACE( REPLACE(REPLACE( REPLACE( 
       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE    
       (REPLACE(REPLACE(REPLACE(REPLACE(UPPER(col_name),'A','A'),'B','A') ,'C','A') ,'D','A') 
       ,'E','A'),'F','A') ,'G','A') ,'H','A') ,'I','A'),'J','A') ,'K','A') ,'L','A') ,'M','A')     
       ,'N','A') ,'O','A') ,'P','A')
       ,'Q','A'),'R','A') ,'S','A') ,'T','A') ,'U','A') ,'V','A') ,'W','A') ,'X','A') ,'Y','A')    
       ,'Z','A'),'8','N'),'7','N'),'6','N'),'5','N'),'4','N'),'3','N')   
       ,'2','N'),'1','N'),'0','N'),'9','N')  
   AS Pattern FROM table_name) A  GROUP BY Pattern;
  </code></pre>
  
  <p>Now, lets say I have a below table</p>
  
  <pre><code>CREATE TABLE Data (
   Column1 varchar(50),
   Column2 varchar(50),
   Column3 varchar(50),
   Column4 varchar(50),
     )
  INSERT INTO Data
   (Column1, Column2, Column3, Column4)
  VALUES 
   (NULL, NULL, 'ABC123', 'abc '),
   ('xyz', NULL, 'MNO300', 'XYZ123 ')
  </code></pre>
  
  <p>Now I want a output like below:</p>
  
  <pre><code>Count_Pat_1 --&gt; Count is of only one data type i.e. either Alphabetic or 
  Numeric
  Count_Part_2 --&gt; Count is of mixed data type i.e. contains both alphabet and 
  number
  Blank --&gt; Count of NULL values
  
  Col_name    Count_Pat_1    Count_Pat_2  Blank
  Column1          1               0       1        
  Column2          0               0       2
  Column3          0               2       0
  Column4          1               1       0 
  </code></pre>
  
  <p>How can I generate the above table? Clearly I need to resort to Dynamic SQL where the entire sql string needs to be set as variable. Below is my approach:</p>
  
  <pre><code>DECLARE @sql nvarchar(max)
  SET @sql = N''
  
  SELECT @sql = @sql + CASE WHEN @stm = N'' THEN N'' ELSE N',' END +
    '('+
    '''' + c.name + ''', '+
    'CASE '+
     'WHEN ' + QUOTENAME(c.name) + ' IS NULL THEN 1 ' +
     'ELSE 0 ' +
    'END, ' +
    'CASE '+
     'WHEN ' + QUOTENAME(c.name) + ' IS NOT NULL THEN LEN(' + QUOTENAME(c.name) + ') - ** the above SQL 
     script ***
    FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
    WHERE t.name = 'Data'
  </code></pre>
  
  <p>But inserting the above SQL script at the place as indicated by ** will not be giving the output properly.</p>
  
  <p>Hence any hints would be appreciated. SQL Server version: 2016</p>