Finding discrepancies between soft link parents and children

oracle

In our system relationships between tables are often (but not always) defined in the application code rather than the database (annoying I know). There's a naming convention which outlines any soft (application side) links.

Here's an example:

On table Oddie, ODD_ID is the Primary key it's got a soft link to table Michael which is called MIC_ODD_ID. There's no Foreign Key constraints between Oddie and Michael as it's defined in the application.

Because of this foreign keys to tables can be created with different amounts of data precision from the "parent" table. So from the previous example, ODD_ID could be a NUMBER(8) and MIC_ODD_ID could be a NUMBER(10).

This is obviously not ideal so I've been tasked with finding all the offending columns. I've been having trouble putting a query together to do this. I'm looking on the DBA_TAB_COLUMNS table and using a substring to outline which columns are similar using this:

SUBSTR(COLUMN_NAME, -0, 6)

Which pulls back the last 6 characters of the COLUMN NAME which should be the same across tables. Using the example from above this would pull back ODD_ID from the Oddie and Michael tables.

I'm having trouble then comparing these strings against each other to find where the data precision isn't the same.

An ideal output would be the offending (Not the same precision) strings and their DATA_PRECISION's along with their OWNER and TABLE_NAME's.

Thanks in advance to anyone who tries to help.

Best Answer

something like below query? adjust code to suit your needs. if you have one column name with multiple definitions, you need to concentrate on those. You didn't mention oracle version so I am going to assume you have access to LISTAGG function in your environment. if not, make suitable amends but this should get you closer. You may optionally want to add views for this comparison as well, but I have only chosen to select tables.

with st as (select c.OWNER ,c.TABLE_NAME, COLUMN_NAME, case when C.NULLABLE = 'N' then 'NOT NULL ' end || 
case when C.DATA_TYPE in ('CHAR','VARCHAR2') then C.DATA_TYPE || ' (' || C.DATA_LENGTH || case when c.char_used = 'C' then ' char' end || ')' 
     when C.DATA_TYPE = 'DATE' then 'DATE' when C.DATA_TYPE = 'TIMESTAMP' then 'TIMESTAMP (' || c.DATA_SCALE || ')' 
     when C.DATA_TYPE = 'NUMBER' and C.DATA_PRECISION is null then 'NUMBER'
     when c.DATA_TYPE = 'NUMBER' and c.DATA_PRECISION is not null then 'NUMBER (' || c.DATA_PRECISION ||',' || NVL(c.DATA_SCALE,'0') || ')'
end Col_def
from DBA_TAB_COLUMNS C join DBA_OBJECTS O on (O.OWNER = C.OWNER and O.OBJECT_NAME = C.TABLE_NAME)
where O.OWNER in (<list your schema owners here>) 
and O.OBJECT_TYPE in ('TABLE') order by 2,3,1)
select column_name,col_def, listagg(table_name,',') within group (order by table_name) tlist 
from st group by column_name, col_def order by 1,2;
Related Question