SQL - Displaying object names and types from user_objects data dictionary

Question

I am using Oracle. I am trying to view a table that looks like this

OBJECT_NAME         OBJECT_TYPE
-----------------   -----------
DEPARTMENT          TABLE
DEPARTMENT_ID_PK    INDEX
EMPLOYEE            TABLE
EMPLOYEE_ID_PK      INDEX

And this is what I have

SELECT OBJECT_NAME, OBJECT_TYPE
FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('EMPLOYEE', 'DEPARTMENT');

But the table that is printed only shows the Department and Employee object name and types, not the department_id_pk and employee_id_pk. What am I missing?

Try WHERE object_name LIKE 'EMPLOYEE%' OR object_name LIKE 'DEPARTMENT%' — Kaushik Nayak
– Kaushik Nayak, Commented Nov 9, 2018 at 10:05

Srini V · Accepted Answer · 2018-11-09 16:18:55Z

1

This will work, if you are using naming standards and including the table name in the index and view names:

select
     object_name,
     object_type
from
     user_objects
where
     (object_name like '%EMPLOYEE%') 
OR   (object_name like '%DEPARTMENT%');

If no such standards are followed then you can use,

select 
    b.uniqueness, 
    a.index_name, 
    a.table_name, 
    a.column_name 
  from 
    user_ind_columns a, 
    user_indexes b 
where 
    a.index_name=b.index_name 
  and 
     ((object_name like '%EMPLOYEE%') or (object_name like '%DEPARTMENT%'))
order by 
    a.table_name, 
    a.index_name, 
    a.column_position;

answered Nov 9, 2018 at 16:18

Srini V

11.5k15 gold badges70 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL - Displaying object names and types from user_objects data dictionary

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related