Hello all. I'm trying to figure out the proper query to return the tables and columns between a foreign key relationship. For instance, the query I'm using now is:

SELECT A.CONSTRAINT_NAME, A.TABLE_NAME,B.COLUMN_NAME, B.COLUMN_NAME AS REFERENCED_COLUMN_NAME, B.TABLE_NAME AS REFERENCED_TABLE_NAME FROM USER_CONSTRAINTS A JOIN USER_CONS_COLUMNS B ON A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME WHERE A.CONSTRAINT_TYPE = 'R' AND (A.TABLE_NAME = @TABLE_NAME);

For example purposes, one of the tables I'm using is named SET_AGENT_LOOKUP_VALS. If I place that into @TABLE_NAME I get:


http://img140.imageshack.us/img140/4788/oraclequeryuv2.jpg


Unfortunately the COLUMN_NAME and REFERENCED_COLUMN_NAME columns both return the parent column with the foreign key relationship. The COLUMN_NAME column should contain the child column with the foreign key relationship. I currently have a SQL Server query that is returning the proper results. I just need to know it's Oracle equivalent. Here's the SQL Server query:

SELECT OBJECT_NAME(FKEYID) AS TABLE_NAME,COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME, COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME FROM SYSFOREIGNKEYS WHERE OBJECT_NAME(FKEYID)=@TABLE_NAME

The result from that display TABLE_NAME as the child table name, COLUMN_NAME as the child column name, REFERENCED_TABLE_NAME as the parent table name, and REFERENCED_COLUMN_NAME as the parent table name. Any help would be appreciated. I'm new when it comes to Oracle (our program supports both Oracle and SQL Server databases and I often only have to edit SQL Server queries.) Thanks.