Hello ,
i have around 800 tables at my oracle database and I would like to ask if there is a method that I can find which tables has the same object id .
i think ,if I read each table then use feature merger between one table and others and so on .it will take time
is there a quick method that compare between all tables and eachother to find which tables has common id and show me the name of tables that has common id.
my target to find the name of tables that has common id.
FME 2021
I would absolutely try to keep the querying within Oracle, rather than read all the data in FME, though I'm not sure how to construct a SQL query with 800 tables
In one SQLExecutor, select all tables which have a column named 'OBJECTID'. You can find this in the database schema tables (In SQL Server this would be in sys.columns or INFORMATION_SCHEMA.COLUMNS) I'm unfamiliar with Oracle but should be something like
select owner, table_name, column_name
from all_tab_columns
where column_name = 'OBJECTID'
That should give you your 800 table names which could have the OBEJCTID you're looking for. From it, you can create 800 SQL statements to pass into a second SQLExecutor
select OBJECTID
from @Value(table_name)
where OBJECTID = @Value(Search_OBJECTID)
In one SQLExecutor, select all tables which have a column named 'OBJECTID'. You can find this in the database schema tables (In SQL Server this would be in sys.columns or INFORMATION_SCHEMA.COLUMNS) I'm unfamiliar with Oracle but should be something like
select owner, table_name, column_name
from all_tab_columns
where column_name = 'OBJECTID'
That should give you your 800 table names which could have the OBEJCTID you're looking for. From it, you can create 800 SQL statements to pass into a second SQLExecutor
select OBJECTID
from @Value(table_name)
where OBJECTID = @Value(Search_OBJECTID)
Thanks for ur idea