title: In SQL Server, how do I retrieve data values from table column names identified in a sub-query?
body: <p>I am using the following SQL to list all table and column names in my schema for tables containing columns whose names contain the string "code" using the following SQL server query:</p>
  
  <pre><code>SELECT 
      a.table_name, a.column_name from (SELECT t.name AS table_name,
      SCHEMA_NAME(schema_id) AS schema_name,
      c.name AS column_name
  FROM 
      sys.tables AS t
  INNER JOIN 
      sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
  WHERE 
      c.name LIKE '%code%') a
  </code></pre>
  
  <p>Result: </p>
  
  <pre><code>Table Name     Column Name
  ----------     -----------
  Tab_1_name     a_code
  Tab_2_name     another_code
  Tab_3_name     yet_another_code
  and so on...
  </code></pre>
  
  <p>I would like to now query the actual data in the a_code and another_code columns using a wrapper but cannot see how to get at the actual data (if doing for Tab  1 individually for example, I would </p>
  
  <pre><code>SELECT a_code FROM Tab_1
  </code></pre>
  
  <p>to get </p>
  
  <pre><code>a_code
  ------
  value 1
  value 2
  value 3
  </code></pre>
  
  <p>but can't figure out or find anywhere how to code the outer query to wrap around the above such that I would get something along the lines of:</p>
  
  <pre><code>Tab1_name  a_code
  ---------  ------
  tab_name 1 value 1
  tab_name 1 value 2
  tab_name 2 value 1
  tab_name 2 value 2
  tab_name 3 value 1
  tab_name 3 value 2 ... etc.
  </code></pre>
  
  <p>i.e. a formatted list of all the data values in all table columns in my schema/DB whose names contain the word "code"?</p>