title: Counting rows for all tables for a particular column and column value
body: <p>I am using SQL Server 2008 and want to get total row count of a column from all tables in a database.</p>
  
  <p>here is my sample data </p>
  
  <p><strong>Table 1</strong></p>
  
  <pre><code>T1Id       Name
  1          XCV
  2          ASD
  3          GHJ
  </code></pre>
  
  <p><strong>Table 2</strong></p>
  
  <pre><code>  T2Id      T1Id       Name
    1         1          TYU
    2         1          BBB
    3         2          LLL
    4         1          JJJ
    5         1          LKM
    6         2          POI
  </code></pre>
  
  <p><strong>Table 3</strong></p>
  
  <pre><code>  T3Id      T1Id       Name
    1         3          DDS
    2         3          BSS
    3         2          SQA
    4         2          FWF
    5         1          IKM
    6         2          PLO
  </code></pre>
  
  <p>I have Table4 also but don't want to add in result count</p>
  
  <p>I am trying </p>
  
  <pre><code>    SELECT     TOP (100) PERCENT ta.name AS TableName, SUM(pa.rows) AS RowCnt
  FROM         sys.tables AS ta LEFT OUTER JOIN
                        sys.partitions AS pa ON pa.object_id = ta.object_id LEFT OUTER JOIN
                        sys.schemas AS sc ON ta.schema_id = sc.schema_id LEFT OUTER JOIN
                        INFORMATION_SCHEMA.COLUMNS ON ta.name = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
  WHERE     (ta.is_ms_shipped = 0) AND (pa.index_id IN (1, 0)) AND (INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = N'T1Id') AND 
                        (ta.name &lt;&gt; N'Table4')
  GROUP BY sc.name, ta.name
  HAVING      (SUM(pa.rows) &gt; 0)
  ORDER BY RowCnt DESC
  </code></pre>
  
  <p>Above SQL giving me result </p>
  
  <pre><code> TableName    RowCnt
   XCV          5
   ASD          5
   GHJ          2
  </code></pre>
  
  <p>but I want to get only XCV (Table1 and T1Id = 1) records counts .
  How to do this with above query or any other suggestion ?</p>
  
  <p>Expected result </p>
  
  <pre><code> Name    RCount
   XCV     5
  </code></pre>
  
  <p>Edit :</p>
  
  <p>I have more then 200 tables in my database and it have T1Id in All tables,
  my aim to find the count of T1Id = 1 from All tables and I dont want to include Table 4 in Results.</p>