sql - Select every row from every table in database where (columnName = value) if column name exists -

i'm using sql server 2005 , logged in sa. query every table in database whether has column name. , if append every row (columnnamevalue = somevalue) results table. return said results table.

there few similar problems solutions out there. notably can use sp_msforeachtable lacks kind of documentation. can use select table_name information_schema.tables list of tables.

the following solution (link text) go every table & every column find value in cell. different finding value in cell if column columnname.

writing nested while statement should possible there inbuilt commands run queries this?

pseudo-code if helps :

foreach(table in tablelist) {     if (table.hascolumnname(some_column) {        exec ('select * table (some_column = some_value)')     } } 

[edit]

rather having single results set single result per table long select returns @ least 1 row. should give large amount of different results expecting join or union work unrealistic.

if possible append tablename start of each result.

i have simple query below gets results display empty tables , doesnt give visual indication in individual results table belongs to:

[further edit]

updated query below include if exist check removes null results & select name source column add table source results @martin

declare @column_value nvarchar(512), @value nvarchar(10);  set @column_value = 'id' set @value = '0';  declare @table_name nvarchar(512), @column_name nvarchar(512), @query nvarchar(512);  set @table_name = '';  while @table_name not null begin     set @table_name =     (         select min(quotename(table_schema) + '.' + quotename(table_name))         information_schema.tables         quotename(table_schema) + '.' + quotename(table_name) > @table_name     );     print 'table name : ' + @table_name;     set @column_name =      (         select min(quotename(column_name))         information_schema.columns         (table_name = parsename(@table_name, 1))             , (column_name = @column_value)      );     print 'column name : ' + @column_name;     if @column_name not null      begin         set @query =              'select ''' + @table_name + ''' source, * ' +             'from ' + @table_name + ' ' +             'where (' + @column_name + ' = ' + @value + ')'         exec         (             'if exists(' + @query + ') ' + @query         )     end      end 

create table foo ( some_column varchar(10) )  create table bar ( some_column varchar(10) )  insert bar values ('some_value')   declare @query nvarchar(max)  select        @query = isnull(@query + ';','') +        'if exists(select * ' + quotename(schema_name(schema_id)) + '.' +        quotename(o.name) + ' some_column=''some_value'')       select ''' + o.name +''' source, * ' + quotename(schema_name(schema_id)) + '.' +        quotename(o.name) + ' some_column=''some_value''' sys.columns c  join sys.objects o on o.object_id = c.object_id o.type in ('u','v') , c.name = 'some_column'  exec sp_executesql @query