Finding constraints in existing database

Finding constraints in existing database

Post by matthew taylo » Sun, 31 Dec 1899 09:00:00



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

 
 
 

Finding constraints in existing database

Post by Kailash Awat » Sun, 31 Dec 1899 09:00:00


Hi Matthew,

Try selecting from USER_CONSTRAINTS (for constraint info) and
USER_CONS_COLUMNS (for tables and column details).

Hope this helps,

Kailash.


>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


 
 
 

Finding constraints in existing database

Post by Kevin Bas » Sun, 31 Dec 1899 09:00:00


Try using DBA_CONSTRAINTS (if you have access to it),  or USER_CONSTRAINTS.
Use the fields of constraint_name, owner, table_name in the WHERE clause for
the table(s) that contain the constraints.


Quote:> 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

 
 
 

Finding constraints in existing database

Post by Mike » Sun, 31 Dec 1899 09:00:00


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


>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