Home » SQL & PL/SQL » SQL & PL/SQL » Getting data from data dictionary
Getting data from data dictionary [message #629353] Mon, 08 December 2014 03:28 Go to next message
internship
Messages: 6
Registered: December 2014
Junior Member
I need to get data from data dictionary for the HR user.
The columns are TABLE_NAME, TABLE_CREATED, COLUMN_NAME, COLUMN_NULLABLE(output yes/no), COLUMN_IS_PK(output yes/no), COLUMN_IS_FK(output yes/no), COLUMN_IS_INDEXED(output yes/no).
And the last column really bugs me...
Re: Getting data from data dictionary [message #629357 is a reply to message #629353] Mon, 08 December 2014 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
And the last column really bugs me...


USER_IND_COLUMNS

Re: Getting data from data dictionary [message #629364 is a reply to message #629357] Mon, 08 December 2014 03:46 Go to previous messageGo to next message
internship
Messages: 6
Registered: December 2014
Junior Member
so far i got:

select a.TABLE_NAME,
a.COLUMN_NAME,
c.CREATED as TABLE_CREATED,
a.NULLABLE as COLUMN_NULLABLE,
B.CONSTRAINT_TYPE as COLUMN_AS_PK,
B.CONSTRAINT_TYPE as COLUMN_AS_FK
from DBA_TAB_COLUMNS a join DBA_CONSTRAINTS B on (a.OWNER=b.OWNER)
join DBA_OBJECTS C on (a.OWNER=C.OWNER)
where a.OWNER ='HR'
order by a.table_name;

how can I get column_as_pk to show only 'P' for primary key and the other one only 'R' for the foreign key?
Re: Getting data from data dictionary [message #629367 is a reply to message #629364] Mon, 08 December 2014 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Your join conditions are wrong.
2/ The solution is far more complex, you also have to use DBA_CONS_COLUMNS

Re: Getting data from data dictionary [message #629368 is a reply to message #629367] Mon, 08 December 2014 03:57 Go to previous messageGo to next message
internship
Messages: 6
Registered: December 2014
Junior Member
Care to help out?
Re: Getting data from data dictionary [message #629373 is a reply to message #629368] Mon, 08 December 2014 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Didn't I already do it?
What is the "primary key" (the list of columns that uniquely defined the rows) for the views? Surely not just "owner".

Re: Getting data from data dictionary [message #629394 is a reply to message #629368] Mon, 08 December 2014 07:28 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
internship wrote on Mon, 08 December 2014 15:27
Care to help out?


Care to follow posting guidelines?
Previous Topic: Retrieve all column except 2 coulmn
Next Topic: Parallel DML problems (update)
Goto Forum:
  


Current Time: Fri Mar 29 05:51:42 CDT 2024