This script gives provides information about the constraints defined for a given table. It accepts the name of the table as an argument. Back to SQL scripts

SET verify OFF
SET lines 150

UNDEFINE table
ACCEPT Table CHAR Prompt "Please enter your table name: "

COLUMN Type Format A4
COLUMN "Condition" Format A30
COLUMN "Column Name" Format A30
TTITLE 'Constraints specified for table &&table'

SELECT cons.constraint_name "Name", cons.constraint_type "Type", cons.search_condition "Condition",
cons.r_owner, cons.r_constraint_name, col.column_name "Column Name", col.position
FROM all_constraints cons, all_cons_columns col
WHERE cons.owner = col.owner
AND cons.table_name = col.table_name
AND cons.constraint_name = col.constraint_name
AND cons.table_name = '&&Table'
/

SET verify ON
SET lines 80

To run the code, cut and paste it onto your favourite editor and save the file to say constraints.sql. At the SQL*PLUS prompt, execute the script using the syntax 'SQL>@constraints'. The script prompts you for the name of the table. It then displays information w.r.t the all constraints created for the table. (If you want to see information about the foreign keys, see the script to find foreign keys and the corresponding information

For the emp table, for example, you get the following output.

SQL>@constraints
Please enter your table name: EMP
---------------
Wed May 09 page 1
Constraints specified for table EMP
NameTypeCondition R_OWNERR_CONSTRAINT_NAMEColumn NamePOSITION
-------------------------------------------------------------------------------------------------------------
PK_EMPP EMPNO1
FK_DEPTNORSCOTTPK_DEPTDEPTNO1

SQL>

Back to SQL scripts
Hosted by www.Geocities.ws

1