title: Count number of rows across multiple tables in one query
body: <p>I have a SQL Server 2005 database that stores data for multiple users. Each table that contains user-owned data has a column called <code>OwnerID</code> that identifies the owner; most but not all tables have this column.</p>
  
  <p>I want to be able to count number of rows 'owned' by a user in each table. In other words, I want a query that returns the names of each table that contains an <code>OwnerID</code> column, and counts the number of rows in each table that match a given <code>OwnerID</code> value.</p>
  
  <p>I can return just the names of the matching tables using this query:</p>
  
  <pre><code>SELECT OBJECT_NAME(object_id) [Table] FROM sys.columns 
      WHERE name = 'OwnerID' ORDER BY OBJECT_NAME(object_id);
  </code></pre>
  
  <p>That query returns a list of table names like this:</p>
  
  <pre><code>+---------+
  |  Table  |
  +---------+
  | Alpha   |
  | Beta    |
  | Gamma   |
  | ...     |
  +---------+
  </code></pre>
  
  <p>But is it possible to write a query that can also count the number of rows in each table that match a given <code>OwnerID</code>? ie:</p>
  
  <pre><code>+---------+------------+
  |  Table  |  RowCount  |
  +---------+------------+
  | Alpha   | 2042       |
  | Beta    | 49         |
  | Gamma   | 740        |
  | ...     | ...        |
  +---------+------------+
  </code></pre>
  
  <p><strong>Note:</strong> The list of table names needs to be returned dynamically, it is not suitable to hard-code  table names into this query. </p>
  
  <hr>
  
  <h2>Edit: the answer...</h2>
  
  <p><em>(I can't edit your answers yet but I can edit my own question so I'm putting it here...)</em></p>
  
  <p>Damien_The_Unbeliever had essentially the correct answer, but SQL Server doesn't allow string concatenation in an <code>exec</code> statement so I had to set the query prior to the <code>exec</code> statement. The final query is as follows:</p>
  
  <pre><code>DECLARE @OwnerID int;
  SET @OwnerID = 1;
  
  DECLARE @ForEachSQL varchar(100);
  SET @ForEachSQL = 'INSERT INTO #t(TableName,RowsOwned) SELECT ''?'', COUNT(*) FROM ? WHERE OwnerID = ' + CONVERT(varchar(11), @OwnerID);
  
  CREATE TABLE #t(TableName sysname, RowsOwned int);
  EXEC sp_MSforeachtable @ForEachSQL, 
      @whereAnd = 'AND o.id IN (SELECT id FROM syscolumns where name=''OwnerID'')';
  SELECT * FROM #t ORDER BY TableName;
  DROP TABLE #t;
  </code></pre>