Oracle SQL - Finding tables with specific column names

One of the tasks I had to do this week on my project was to update all case number's to a new target case number in an Oracle Database.  Because the project is still growing and functionality will be continued to be added to, I needed to find a dynamic way to do this.

Fortunately our database schema has a common column naming convention for case numbers, they all look similar to <item>_case_name, you'll notice the <item> can be different though.

Below are the two Oracle SQL queries I used to accomplish this:
1. Find all tables that start with CHERRYSHOE in the database schema that contain column name like <item>_case_name

select table_name from user_tab_columns
inner join user_objects on user_tab_columns.table_name=user_objects.object_name
where user_tab_columns.column_name like '%_CASE_NAME' and user_objects.object_type='TABLE'
and table_name like 'CHERRYSHOE_%'
order by table_name;

It'll return you something like the following:

CHERRYSHOE_BARCODE
CHERRYSHOE_CASE
CHERRYSHOE_DOCUMENT_CASE_FILE
CHERRYSHOE_REPORT
CHERRYSHOE_TASK_CASE_FILE

  5 rows selected

Once you grab those tables, you'll want to do the following for each table.

2. Find the <item>_case_name column name in the table

SELECT column_name FROM all_tab_cols where table_name ='CHERRYSHOE_CASE' and column_name like '%_CASE_NAME';

It'll return you something like the following:

COLUMN_NAME
------------------------------
ITEM1_CASE_NAME

Once you have both those pieces of information, I wrote the appropriate UPDATE statements for all applicable tables in a transaction since the database schema has constraints across tables on the <item>_case_name column.