CODEWITHSUNDEEP
sqlsearchfirebird
Roberto palmer
Roberto palmer

Reputation: 25

How to find a value in all tables on firebird?

How to search for a value in all tables on Firebird?

Knowing a value, I need to find all tables, columns in which it occurs. Someone can help me please? I have no clue where to start.

I'm using Firebird 3.0.

Upvotes: 2

Views: 658

Answers (2)

Mehrzad Khoddam
Mehrzad Khoddam

Reputation: 1

If you are using FlameRobin, you need to do some modifications to get it working:

set term !!;
execute block
returns (
    table_name varchar(63),
    column_name varchar(63))
as 
declare variable search_value varchar(30) = 'John';
declare variable has_result boolean;
begin
    for select trim(r.rdb$relation_name), trim(f.rdb$field_name)
        from rdb$relation_fields f
        join rdb$relations r on f.rdb$relation_name = r.rdb$relation_name
        and r.rdb$view_blr is null 
        and (r.rdb$system_flag is null or r.rdb$system_flag = 0)
        order by r.rdb$relation_name, f.rdb$field_position 
        into :table_name, :column_name
    do
    begin
        execute statement ('select exists(select * from ' || table_name || ' where ' || column_name || ' = ?) from rdb$database') (search_value) 
            into has_result;
        if (has_result) then
            suspend;
        when any do
        begin 
            /* value not comparable with varchar, skip */
        end
    end
end

Upvotes: 0

Mark Rotteveel
Mark Rotteveel

Reputation: 109015

There is no built-in way to do that, you will need to explicitly query all (relevant) columns of all tables to achieve this. Taking inspiration from the not working code by kamil in Find tables, columns with specific value, you could do something like:

execute block
returns (
    table_name varchar(63),
    column_name varchar(63))
as 
declare search_value varchar(30) = 'John';
declare has_result boolean;
begin
    for select trim(r.rdb$relation_name), trim(f.rdb$field_name)
        from rdb$relation_fields f
        join rdb$relations r on f.rdb$relation_name = r.rdb$relation_name
        and r.rdb$view_blr is null 
        and (r.rdb$system_flag is null or r.rdb$system_flag = 0)
        order by r.rdb$relation_name, f.rdb$field_position 
        into :table_name, :column_name
    do
    begin
        execute statement ('select exists(select * from "' || table_name || '" where "' || column_name || '" = ?) from rdb$database') (search_value) 
            into has_result;
        if (has_result) then
            suspend;
        when any do
        begin 
            /* value not comparable with varchar, skip */
        end
    end
end

This identifies which table + column is equal to search_value (but you can of course use a different condition than =, e.g. containing ? if you want to check for columns that contain search_value).

The above could be further refined by only selecting columns of an appropriate type, etc. And of course, the varchar(30) might not be suitable or sufficient for all situations.

You could also change this to a stored procedure, e.g. by changing the header to

create procedure search_all(search_value varchar(30))
returns (
    table_name varchar(63),
    column_name varchar(63))
as 
declare has_result boolean;
begin
  -- ... rest of code above

You can then execute it with:

select * from search_all('John')

Upvotes: 1

Related Questions