retrieve constraints definition

[lovekang]

how to retrieve oracle constraints definitions?
like pk, fk, not null, default etc.

Thanks

 

[SantaMufasa]

LoveKang,

My response from last week to thread759-1427338 contains what you want...specifically, my response to MarieAnnie's Item # 2, "I would like to display the complete structure of a table." The script I posted lists all constraints for any table.

DEFAULT settings are not contraints, so they do not appear as part of the results of that script. If you wish to see a default value for a column, you can use my "show_defaults.sql" script:

set verify off
prompt Enter the name of the Oracle user that owns 
accept owner_name prompt "    the table with DEFAULTs to check : "
prompt
accept tab_name prompt "Enter the name of &owner_name's table to check for DEFAULT values: "
select owner,table_name,column_name,data_default
from all_tab_cols
where upper('&owner_name') = owner
  and upper('&tab_name') = table_name
  and data_default is not null;

Since the code, above, contains SQL*Plus "ACCEPT...PROMPT" code, you cannot simply copy-and-paste the code to a SQL*Plus prompt; you must run the code from a script such as "@show_defaults".

Here is sample output from running "show_defaults.sql":

@show_defaults
Enter the name of the Oracle user that owns
    the table with DEFAULTs to check : test

Enter the name of test's table to check for DEFAULT values: task_type

OWNER           TABLE_NAME      COLUMN_NAME     DATA_DEFAULT
--------------- --------------- --------------- ---------------
BGGT            USERFIELD       REQUIRED        'F'

As you can imagine, you can slightly adjust the script, above, to show all non-null DEFAULTs, for all COLUMNs, in all TABLEs, in the entire database if you wish.


Let us know your reactions to the above.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

Look at the user|all|dba_constraints tables

 

[sandtek]

you can also look at user/all/dba_cons_columns