CODEWITHSUNDEEP
sqloracle-databaseobject
K. Murphy
K. Murphy

Reputation: 3

SQL - Displaying object names and types from user_objects data dictionary

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?

Upvotes: 0

Views: 1365

Answers (1)

Srini V
Srini V

Reputation: 11365

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;

Upvotes: 1

Related Questions