Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Question Unanswered: Primary and Foreign Keys

    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

  2. #2
    Join Date
    Sep 2003
    Location
    Brussel
    Posts
    52
    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.
    A good programmer is a LAZY programmer!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •