5.6. Data Dictionary Constraint Scripts
5.6.1. Constraints on a Table
The following query is a general script to query the constraints of a table. The use of the script accepts a table name as a command line argument.
SQL> @my_const_are table_name
Following the script text is the SQL*Plus output from the STUDENTS table.
-- Filename: my_const_are.sql set verify off column column_name format a15 column table_name format a12 set pagesize 1000 SELECT SUBSTR(A.column_name,1,30) column_name, DECODE(B.constraint_type, 'P', 'PRIMARY KEY', 'U', 'UNIQUE KEY', 'C', 'CHECK OR NOT NULL', 'R', 'FOREIGN KEY') constraint_type, A.constraint_name FROM user_cons_columns A, user_constraints B WHERE A.table_name = UPPER('&1') AND A.table_name = B.table_name AND ...Get Programming Oracle® Triggers and Stored Procedures, Third Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
