Monday, January 14, 2013

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:

  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:

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.

No comments:

Post a Comment

I appreciate your time in leaving a comment!