CODEWITHSUNDEEP
oracle-databaseoracle11g
Ian R. O'Brien
Ian R. O'Brien

Reputation: 6920

How can I find column level references to a table?

I have a table called IdMap under schema ABC_OWNER. I have a stored procedure called sp_MyProcedure that has this query, and this is in schema XYZ_OWNER:

SELECT M.IssueId
FROM IdMap M Join Product P on M.IssueId=P.IssueId

I would like to write a query that will tell me all references to IdMap and specifically which columns are being referenced. So the results of this script would tell me that IssueID of table IdMap is being referenced by stored procedure sp_MyProcedure.

I tried querying in the all_dependencies table and also against all_objects, but I don't know how to get into this at the column level. I am coming from a SQL Server background.

Upvotes: 1

Views: 219

Answers (2)

Jon Heller
Jon Heller

Reputation: 36807

Try Rob Van Wijk's DBA_DEPENDENCY_COLUMNS:

create user abc_owner identified by abc_owner;
create table abc_owner.IdMap(IssueId number, a number);
create table abc_owner.Product(IssueId number, a number);

create user xyz_owner identified by xyz_owner;
grant select on abc_owner.IdMap to xyz_owner;
grant select on abc_owner.Product to xyz_owner;

create or replace procedure xyz_owner.sp_myProcedure is
    v_issue_id number;
begin
    SELECT M.IssueId
    into v_issue_id
    FROM abc_owner.IdMap M Join abc_owner.Product P on M.IssueId=P.IssueId;
end;
/

select name, referenced_name, referenced_column
from sys.dba_dependency_columns
where owner in ('ABC_OWNER', 'XYZ_OWNER');


NAME            REFERENCED_NAME  REFERENCED_COLUMN
----            ---------------  -----------
SP_MYPROCEDURE  PRODUCT          ISSUEID
SP_MYPROCEDURE  IDMAP            ISSUEID

Upvotes: 2

Guido Leenders
Guido Leenders

Reputation: 4262

This is not possible with Oracle at this moment. Since 11g oracle considers column usage information for invalidating objects but as far as I know this information is not officially available outside of the Oracle kernel.

Upvotes: 1

Related Questions