We have over 180 processes created using GEL. We would like to find in all the processes where one of the column of "odf_ca_project" table is being used. Anybody done this before? Any clues will be appreciated.
Do a search to the scripts table (something like CMN_SCRIPTS or CMN_CUSTOM_SCRIPTS) then trace them to the validated and active process through process steps, process stages and process versions.
select * from cmn_custom_scripts
where script_text like '%odf_ca_project%'
That will pick the table. You could also search for the field. Once you find the matches you have to manually verify if they are true or false positives That is if the field is in the ODF_CA_PROJECT table or for some other table and that it is the exact string and not a pari in a longer string. You have to use the like %% construct to find the matches in the script text and that will pick also those false matches and occurences where the field name is pre or post suffixed with thinks like (). which are valid matches.
With 180 scripts you are better of querying just for the script ID's and once you got them then export the script text to validate the matches before you start tracking the processes.
This might be better if you're on Oracle (I vaguely recall that the SQL Server collation for Clarity is case insensitive)...
where LOWER(script_text) like '%odf_ca_project%'
Thanks Aurora and Urmas for your very quick response.
There is no ER for processes any more in the Tech ref.
There is one in
but that goes more to instances that have run and not so much to definitions, but you could replace run with def in the table name. There is a better one in the Tech ref of 7.5 if you can get hold of that.
Anyway the tables are listed in the thread.
Retrieving data ...