読者です 読者をやめる 読者になる 読者になる

sqlplusでスキーマの持つインデックスの一覧を見たいんです

USER_INDEXESとUSER_IND_COLUMNSから引っ張ってくる。

SQL> conn hr/hr@xe
SQL> col table_name  format a16 trunc
SQL> col index_name  format a24
SQL> col uniqueness  format a4  trunc
SQL> col column_name format a24
SQL> select i.table_name, i.index_name, i.uniqueness, c.column_name
  2  from user_indexes i, user_ind_columns c
  3  where i.index_name = c.index_name
  4  order by index_name,table_name,column_position
  5  ;

TABLE_NAME       INDEX_NAME               UNIQ COLUMN_NAME
---------------- ------------------------ ---- ------------------------
COUNTRIES        COUNTRY_C_ID_PK          UNIQ COUNTRY_ID
DEPARTMENTS      DEPT_ID_PK               UNIQ DEPARTMENT_ID
DEPARTMENTS      DEPT_LOCATION_IX         NONU LOCATION_ID
EMPLOYEES        EMP_DEPARTMENT_IX        NONU DEPARTMENT_ID
EMPLOYEES        EMP_EMAIL_UK             UNIQ EMAIL
EMPLOYEES        EMP_EMP_ID_PK            UNIQ EMPLOYEE_ID
EMPLOYEES        EMP_JOB_IX               NONU JOB_ID
EMPLOYEES        EMP_MANAGER_IX           NONU MANAGER_ID
EMPLOYEES        EMP_NAME_IX              NONU LAST_NAME
EMPLOYEES        EMP_NAME_IX              NONU FIRST_NAME
JOB_HISTORY      JHIST_DEPARTMENT_IX      NONU DEPARTMENT_ID

TABLE_NAME       INDEX_NAME               UNIQ COLUMN_NAME
---------------- ------------------------ ---- ------------------------
JOB_HISTORY      JHIST_EMPLOYEE_IX        NONU EMPLOYEE_ID
JOB_HISTORY      JHIST_EMP_ID_ST_DATE_PK  UNIQ EMPLOYEE_ID
JOB_HISTORY      JHIST_EMP_ID_ST_DATE_PK  UNIQ START_DATE
JOB_HISTORY      JHIST_JOB_IX             NONU JOB_ID
JOBS             JOB_ID_PK                UNIQ JOB_ID
LOCATIONS        LOC_CITY_IX              NONU CITY
LOCATIONS        LOC_COUNTRY_IX           NONU COUNTRY_ID
LOCATIONS        LOC_ID_PK                UNIQ LOCATION_ID
LOCATIONS        LOC_STATE_PROVINCE_IX    NONU STATE_PROVINCE
REGIONS          REG_ID_PK                UNIQ REGION_ID

21行が選択されました。

参考