Re: Finding constraints in existing database

From: Mike S. <mis1965_at_yahoo.com>
Date: Sat, 04 Mar 2000 05:02:40 GMT
Message-ID: <38c0988f.48968581@news.metro.net>

Matthew,

I think this script does what you're looking for.

• File: SHOWCONS.SQL Created: 03/02/00
• Updated: 03/02/00
• Show constraints.

CLEAR COLUMNS
CLEAR BREAKS COLUMN table_name FORMAT a20 HEADING 'Table' WORD_WRAP COLUMN constraint_name FORMAT a20 HEADING 'Constraint Name' WORD_WRAP COLUMN cons_type FORMAT a14 HEADING 'Type' COLUMN search_condition FORMAT a30 HEADING 'Search Condition' WORD_WRAP BREAK ON table_name

SELECT table_name, constraint_name,

	DECODE(constraint_type, 'P', 'Primary Key',
				'U', 'Unique Key',
				'R', 'Foreign Key',
				'C', 'Check/Not Null',
				     constraint_type) cons_type,
	search_condition

FROM user_constraints
ORDER BY table_name, constraint_name;

Mike S.

On Fri, 03 Mar 2000 17:22:53 +1000, matthew taylor <s176226_at_student.uq.edu.au> wrote:

>a while ago i discovered
>select * from USER_TABLES;
>
>and the resulting flood of information when filtered yielded all kinds
>of interesting information.... like all the names of tables in the table
>space etc.
>
>I'm still wading through various manuals to get the hang of things in
>Oracle but I'm curious, is there a quick and easy way to find
>constraints that have been setup on a database?? ie. is it something
>like
>
>select blah from constraints_blah ???
>
>
>Got myself all happy the other day when I jdbc connected across network
>to an access database, now to move onto a jdbc connection across
>webserver and do some dynamic content in web pages.
>
>
>Matthew
Received on Fri Mar 03 2000 - 23:02:40 CST