How to distinguish between indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to distinguish between indexes

  1. #1
    Join Date
    Sep 2005
    Posts
    46

    How to distinguish between indexes

    Dear All,
    I'm creating a utility to compare tables in two different schemas of a database. I need to compare the indexes as well. For that I'm using the static views all_ind_columns and user_ind_columns. My problem is that i'm not able to distinguish between Primary Keys and other indexes. In user_indexes only index name and index columns are given. Is there any way to obtain all primary key indexes and other indexes in a schema seperatly.


    Regards
    Issac

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool


    You need to look at ALL_CONSTRAINTS to know which indexes are primary (CONSTRAINT_TYPE='P') and unique (CONSTRAINT_TYPE='U')

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Sep 2005
    Posts
    46
    Thanks a lot LKBrwn_DBA!!!!

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Technically an index is used to support a primary key, and is not of a primary key type itself. It doesn't have to be unique, and it doesn't have to have the same columns in the same order either.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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



Click Here to Expand Forum to Full Width