sql - Counting rows for all tables for a particular column and column value -

i using sql server 2008 , want total row count of column tables in database.

here sample data

table 1

t1id       name 1          xcv 2          asd 3          ghj 

table 2

  t2id      t1id       name   1         1          tyu   2         1          bbb   3         2          lll   4         1          jjj   5         1          lkm   6         2          poi 

table 3

  t3id      t1id       name   1         3          dds   2         3          bss   3         2          sqa   4         2          fwf   5         1          ikm   6         2          plo 

i have table4 don't want add in result count

i trying

    select     top (100) percent ta.name tablename, sum(pa.rows) rowcnt         sys.tables ta left outer join                       sys.partitions pa on pa.object_id = ta.object_id left outer join                       sys.schemas sc on ta.schema_id = sc.schema_id left outer join                       information_schema.columns on ta.name = information_schema.columns.table_name     (ta.is_ms_shipped = 0) , (pa.index_id in (1, 0)) , (information_schema.columns.column_name = n't1id') ,                        (ta.name <> n'table4') group sc.name, ta.name having      (sum(pa.rows) > 0) order rowcnt desc 

above sql giving me result

 tablename    rowcnt  xcv          5  asd          5  ghj          2 

but want xcv (table1 , t1id = 1) records counts . how above query or other suggestion ?

expected result

 name    rcount  xcv     5 

edit :

i have more 200 tables in database , have t1id in tables, aim find count of t1id = 1 tables , dont want include table 4 in results.

it seems want 'generically' find values in table column named t1id value of 1 (except table4, , guess you'll want exclude table1 row counts well, seems 'master' reference table of column value-name mappings).

afaik need resort dynamic sql in order apply filter.

unfortunately your sample query misleading - query returns

table2  6 table3  6 table1  3 

which overall rowcounts of tables obtained sys.partitions.rows (i.e. not filtered in anyway).

i believe approach needed is, after finding 'eligible' tables containing column of interest, need doing querying each table, , filtering each required predicate (i.e. t1id = '1'). need sum of these results project final result.

my solution horrid contemplate posting here, i've put fiddle here , on gist here. basically, generates following dynamic sql:

with summedcte  (      select t1id, count(*) rowintable  [dbo].[table2] group t1id       union       select t1id, count(*) rowintable  [dbo].[table3] group t1id  ) select t.name, t.t1id, sum(c.rowintable) rowsinalltables     summedcte c     inner join table1 t       on c.t1id = t.t1id     t.name = 'xcv'     group t.name, t.t1id; 

which can tailor meet predicate needs.

edit - caveat

after seeing op's comment, viz intended usage enforce 'may not delete if in parent row in use' business rule in application, clear use of cursor , loop not fit production.

the preferred approach should along lines of orm or other productivity tool:

• use dynamic sql hack build cte based query (i.e. instead of sp_executesql @sql, print @sql , grab resultant sql)
• the resultant cte (union of ~200 tables) can baked application enforce business rule.
• however, each time relevant database ddl changed, cte need regenerated , re-absorbed app e.g. build avoided e.g. retaining cte query tokenized script.