Todos los Sábados a las 8:00PM

Ahora a suscribirse y seguir el contenido de este blog.

miércoles, 14 de junio de 2017

List the indexes associated with tables in a schema

Simple mode using "LISTAGG".




select table_name,
listagg(index_name, ','
on overflow truncate
) within group (order by index_name) inds
from all_indexes
where owner='HR'
group by table_name;






TABLE_NAME       INDS
---------------- -----------------------------------------------------------------------
COUNTRIES        COUNTRY_C_ID_PK
DEPARTMENTS      DEPT_ID_PK,DEPT_LOCATION_IX
EMPLOYEES        EMP_DEPARTMENT_IX,EMP_EMAIL_UK, EMP_EMP_ID_PK, EMP_JOB_IX,EMP_MANAGER_IX,EMP_NAME_IX
JOBS             JOB_ID_PK
JOB_HISTORY      JHIST_DEPARTMENT_IX,JHIST_EMPLOYEE_IX, JHIST_EMP_ID_ST_DATE_PK,JHIST_JOB_IX
LOCATIONS        LOC_CITY_IX,LOC_COUNTRY_IX,LOC_ID_PK, LOC_STATE_PROVINCE_IX
REGIONS          REG_ID_PK

7 rows selected.

No hay comentarios:

Publicar un comentario

Te agradezco tus comentarios. Te esperamos de vuelta.