How can I search some particular character on the entire database with IBexpert?
For example:
I have some "'" (') in some part in the data of the tables and this is getting me an error later on, and I have many tables to search manually... How can I do it?
Thx
The best I can think is you may generate your own script and run it against the database. Fro this case isql (command text line tool for firebird) is better than IBExpert to accomplish this.
So, first step is to go and find all the char or varchar columns and construct a custom query for each field in each table. Save this script as create_search_script.sql in the same directory as your database (or in any directory if you connect to a remote database).
set heading off;
set blob off;
set width sql 400;
select '--tables with a primary key' from rdb$database;
select
trim(
cast(
'select '
||''''
||trim(rf.rdb$relation_name)
||''''
||', '
||(select list(trim(isg.rdb$field_name))
from rdb$index_segments isg
where isg.rdb$index_name = (select rc.rdb$index_name
from rdb$relation_constraints rc
where rc.rdb$relation_name = rf.rdb$relation_name
and rc.rdb$constraint_type = 'PRIMARY KEY'))
||', '
||trim(rf.rdb$field_name)
||' from '
||trim(rf.rdb$relation_name)
||' where '
||trim(rf.rdb$field_name)
||' like ''%''''%'';'
as varchar(2000))
) sql
from rdb$relation_fields rf
inner join rdb$relations r on r.rdb$relation_name = rf.rdb$relation_name
inner join rdb$fields f on f.rdb$field_name = rf.rdb$field_source
inner join rdb$types t on t.rdb$field_name = 'RDB$FIELD_TYPE' and t.rdb$type = f.rdb$field_type
where t.rdb$type_name = 'TEXT'
and coalesce(r.rdb$system_flag, 0) != 1
and exists (select 1
from rdb$relation_constraints rc
where rc.rdb$relation_name = rf.rdb$relation_name
and rc.rdb$constraint_type = 'PRIMARY KEY'
)
;
select '--tables without a primary key' from rdb$database;
select trim(
'select '
||''''
||trim(rf.rdb$relation_name)
||''''
||', tbl.*'
||' from '
||trim(rf.rdb$relation_name)
||' tbl where '
||trim(rf.rdb$field_name)
||' like ''%''''%'';'
) sql
from rdb$relation_fields rf
inner join rdb$relations r on r.rdb$relation_name = rf.rdb$relation_name
inner join rdb$fields f on f.rdb$field_name = rf.rdb$field_source
inner join rdb$types t on t.rdb$field_name = 'RDB$FIELD_TYPE' and t.rdb$type = f.rdb$field_type
where t.rdb$type_name = 'TEXT'
and coalesce(r.rdb$system_flag, 0) != 1
and not exists (select 1
from rdb$relation_constraints rc
where rc.rdb$relation_name = rf.rdb$relation_name
and rc.rdb$constraint_type = 'PRIMARY KEY'
)
;
now, start a new command session, go (cd) to that folder and run this commands:
del search_results.txt
del search_script.sql
isql your-db.fdb -u sysdba -p masterkey -o search_script.sql -i create_search_script.sql
isql your-db.fdb -u sysdba -p masterkey -o search_results.txt -i search_script.sql
if you're on linux, the isql tool is named isql-fb in some distros (I think after firebird 1.5, not sure about it).
Replace with your own database name, user name and password on the command line before run.
Now, the search_results.txt file will have a list of all matching records for the whole database.
warning be careful... if you have a file named search_script.sql or search_results.txt on that folder... change the file names before running the commands or adapt the commands to use other file names.
-o modifier for isql command line tool doesn't overwrite a file, thus the need to first delete the file in order to have a fresh script and a fresh report.
The script was tested against a firebird 2.1 server in windows, but it will work for most firebird versions and platforms.
Enjoy!