Value of particular column from all (some) tables in database dynamically in Firebird

A question came to me last week. It was simple. Given the column I’d like to query all tables in database for this column (with some condition) and get values back. It was on Firebird so I jumped into system tables and generated query on the fly in execute block (aka anonymous stored procedure).

The idea is simple. First get all table names (views and system tables excluded, but you can also exclude i.e. temporary tables) with this column (to be able to later run the query successfully), then concatenate some strings to build the query (with condition for the column) and finally use execute statement to run the query. The into clause will fill the variable and suspend will send the result (row) to client.

execute block returns (table_name varchar(100), column_value varchar(100)) as declare variable column_name varchar(100); begin column_name = upper('id'); /* put here your column name */ for select rdb$relation_name from rdb$relations r where rdb$system_flag = 0 /* no system tables */ and rdb$view_blr is null /* no views */ and exists(select 1 from rdb$relation_fields rf where rf.rdb$relation_name = r.rdb$relation_name and rf.rdb$field_name = :column_name) into :table_name do begin execute statement 'select cast(' || column_name || ' as varchar(100)) from ' || table_name || ' where /* put your condition here */' into :column_value; suspend; end end