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:
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
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
MIC_ODD_ID could be a
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
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.