CODEWITHSUNDEEP
sqldatabaseoracle-databasedatabase-designdata-structures
Carlos_Blive
Carlos_Blive

Reputation: 23

How can I see where data in a column comes from?

I don't know if anyone can help me. In my job, I inherited a completely undocumented database (Oracle 11). So far, I've managed to map most of the tables and determine what's going on where. However, there are a few columns that I haven't been able to decipher.

Is there some way of finding out how is the data in the column built? This is not a manual input. Everything seems to point to the data being the result of an entry in a different column in a completely different table.

It might be an impossible task, but any and all suggestions will be more than welcome.

Thanks!

C

Upvotes: 1

Views: 383

Answers (3)

Michael Ballent
Michael Ballent

Reputation: 1088

I am presuming that you already have the sql that is in question. 

select table_name from dba_tab_columns
where column_name = 'COLUMN_YOU_WANT_TO_KNOW'

will provide all tables that contain a column name that you are looking for. If you do not have dba privileges you can use all_tab_columns instead (which will show all tables your account would have access to).

Upvotes: 0

DCookie
DCookie

Reputation: 43533

Perhaps the data is being inserted in your mystery columns via a trigger? Try looking in the PL/SQL source table in the dictionary:

SELECT owner, name, type, line
  FROM dba_source
 WHERE UPPER(text) LIKE '%MYSTERY_COLUMN_NAME%'
   AND type = 'TRIGGER';  -- use or omit this as desired.

This will get you pointed in some possible places to look.

Good luck!

Upvotes: 2

user330315
user330315

Reputation:

You can retrieve the complete DDL for a table using the DBMS_METADATA package. 

SELECT dbms_metadata.get_ddl('TABLE', 'YOUR_TABLE_NAME', 'YOUR_USER_NAME')
FROM dual;

If those columns are really computed columns then this should be visible in the DDL for the table.

Alternatively you can use SQL Developer to show the DDL for the table

Upvotes: 1

Related Questions