title: How to count occurrences of value in all tables that have a specific column?
body: <p>I have a database with hundreds of tables, some having a column named <code>column1</code>. I want to count occurrences of a specific value in these tables like this:</p>
  
  <pre><code>Table  | Count
  -------|------
  table1 |  0
  table2 |  5
  ...
  </code></pre>
  
  <p>First part (finding list of tables) is done using the code from <a href="https://stackoverflow.com/a/26293125/2093077">here</a>. But I don't know how to put these names in a query. As explained in <a href="https://stackoverflow.com/a/2838507/2093077">another question</a>, table names and column names should be static in a static query. What I get from that answer is that I have to generate some tsql like:</p>
  
  <pre><code>SELECT 'table1' AS [Table], count(*) AS [Count] FROM table1 WHERE column1 = somevalue
  UNION SELECT 'table2' AS [Table], count(*) AS [Count] FROM table2 WHERE column1 = somevalue
  UNION ...
  </code></pre>
  
  <p>and then run it using <a href="https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-2017" rel="nofollow noreferrer">EXEC</a> or <a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017" rel="nofollow noreferrer">sp_executesql</a>.</p>
  
  <p>So, are there any other options to automate this?</p>