Re: retrieve foreign keys list

Jean wrote:

> Hi to all!!
> I have an Oracle 8 database.
> No, I'm implementing an import operation from an ASP page.
> Because there are some relationships between tables when I try to delete
> rows before import or I try to insert rows in a table without related rows
> in the other table I receive an error.
> So I decide to drop all foreign keys from destination database, import data
> and then add foreign keys.
> It is good, but I don't say how I can retrieve foreign keys list for tables.
> In SQL Server 2000 there is a stored procedure called sp_fkeys where passing
> table name I have for result a recordset with all foreign keys for specified
> table.
> Does exist a similar stored procedure, function or SQL statement that is
> able to retrieve table's foreign keys?
> Remember that I need to use this function from ASP so I need a statement
> that I can use from ADO.
>
> Please help me.
> Thanks in advance.
> Bye

The information you are looking for is contained in the data dictionary in USER_CON_COLUMNS. Oracle assumes that those that use it don't need wizards so such stored procedures don't exist ... unless you write them yourself. I strongly urge you to acquaint yourself with the data dictionary. It has capabilities SQL Server 2000 can only dream of.

Perform the following query:

SELECT object_name
FROM dba_objects
WHERE object_name LIKE 'DBA%';

this will give you a good starting point.

Daniel A. Morgan Received on Sat Jul 21 2001 - 23:52:25 CEST