title: SQL Server Select from a table returned by a query as list of table names
body: <p>If I have these tables below:</p>
  
  <p><strong>PLAYERS</strong></p>
  
  <pre><code>ID    Name
  ==    ===========
  1     Mick
  2     Matt
  </code></pre>
  
  <p><strong>COACHES</strong></p>
  
  <pre><code>ID    Name
  ==    ===========
  1     Bill
  2     Don
  </code></pre>
  
  <p>And I have a script below to find all tables that has a column called "Name":</p>
  
  <pre><code>SELECT t.name AS table_name FROM sys.tables AS t
  INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
  WHERE c.name LIKE 'Name'
  </code></pre>
  
  <p>Which returns the following:</p>
  
  <pre><code>table_name
  ===========
  PLAYERS
  COACHES
  </code></pre>
  
  <p>How can I select all the rows from both tables returned by the query above?</p>