Re: retrieve foreign keys list

PT wrote:

> "Jean" <ken_jean_at_hotmail.com> wrote in message
> news:9gaott$bcv$1_at_serv1.iunet.it...
> > 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
> >
> >
> Jean,
>
> Try this sql
>
> select table_name,constraint_name,column_name from user_cons_columns
> where constraint_name like '%FK%';
>
> Remember you should have priveleges on the view user_cons_columns to use
> this.
>
> HTH
> PT

It is impossible to not have access to user_ and all_ views unless the only privilege you have is create session. SELECT from these is standard in the GRANT CONNECT role.

Daniel A. Morgan Received on Sat Jul 21 2001 - 16:52:42 CDT