Oracle data dictionary Hello everybody (Happy coding and merry source code for 2005! )

Today I would like to talk about Oracle data Dictionary.
Without any administration tool it's possible to retrieve information about tables,views and constraints.
To understand this article you need an Oracle client connected to a valid schema.
(SQL+ for instance)

First I suggest you to try this syntax:
select * from DICT
This query returns all the views dealing with this schema's dictionary, very useful to retrieve the exact name of a view. The dictionary contains a big amount of views, let's focus on a few one:

Imagine you want to list all the tables you can acces in a schema, you may then try the following:
SELECT * FROM ALL_TABLES

If you're not a DBA but a developer you may just be interested in your own tables. So try this one:
SELECT * FROM ALL_TABLES
WHERE OWNER ='My_name'

The same thing about views:
SELECT * FROM ALL_VIEWS
WHERE OWNER ='My_name'

Now imagine you need to know all the constraints of a table :
SELECT * FROM ALL_CONSTRAINTS
WHERE
table_name= 'My_table'

That is useful, but you may want something more fine tunned:
What are the foreign keys?:
SELECT * FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE= 'R'
and table_name= 'My_table'

What is the primary key? :
SELECT * FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE= 'P'
and table_name= 'My_table'

That's interesting but most of the time you need the name of a field. So, the following retrieves the list of all the fields included in the primary key:
SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS CC, all_constraints C
where CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME
and C.TABLE_NAME= 'My_Table'
and C.CONSTRAINT_TYPE='P'

That's all folks !
Hope this helps, comments are welcome!
  ¶ 8:42 AM