Hey all,

I'm sure this is elementary but, does anyone have a basic SQL that will list primary keys and foreign keys for a set of tables? I've been asked to use SQL*Plus to sort through several hundred tables. I can write and run simple queries but I've never had so many tables to deal with. Thanks for any direction you can offer.

Dee

I hope I understand this right. You want to look at your primary and foreign keys? In oracle there's a table user_constraints.

SQL> desc user_constraints
Naam Null? Type
----------------------------------------- -------- ---------------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE


This table contains all the primary keys, foreign keys, check's you've put on a table. To view those constraints do:

SELECT * FROM user_constraints;

Hope I helped you out.