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
As a modification you can also instead of running n queries create one big string with union all-ing all queries and run just this one. You should compare execution plans and speed to see which one performs better. Then you would use for execute statement ... do do process results.
