sql - How to find List Tables using columns value -

i have 500+ tables in database. tables have several columns. among them tables have 'cmdflag' column , value of columns may have 'c'or'd' or 'm'.

my requirement find list of tables cmdflag 'c'or'd' or 'm'.

table name    column name   value ----------     -----------   ----- table_a         cmdflag      c table_a         cmdflag      d table_a         cmdflag      m table_b         cmdflag      c table_b         cmdflag      d table_c         cmdflag      m 

so on ...

i can find list of tables these have cmdflag column using information_schema.columns. want find list of tables cmdflag columns have value 'c'or'd' or 'm'.

i have gone through several questions can't fulfill requirement. want use simple query not procedure.

try this. have use dynamic query , temp tables exec not work common table expressions.

create table #t1 (     tablename varchar(30),     rn int )  create table #t2 (     tablename varchar(30),     columnname varchar(30),     value char(1) )  insert #t1 select c.table_name, row_number() over(order table_name) rn information_schema.columns c c.column_name = 'cmdflag'  declare @count int = (select count(1) #t1) declare @iterator int = 1 declare @tablename varchar(30) declare @script  varchar(200)  while @iterator <= @count begin     select @tablename = (select tablename #t1 rn = @iterator)     select @script = 'select '''+@tablename+''', ''cmdflag'', cmdflag '+@tablename+' group cmdflag'      insert #t2     exec(@script)      select @iterator = @iterator + 1  end  select * #t2  drop table #t1 drop table #t2