Home » SQL & PL/SQL » SQL & PL/SQL » Constraints (Oracle 9i)
Constraints [message #320000] Tue, 13 May 2008 12:11 Go to next message
kimbeng
Messages: 4
Registered: March 2008
Location: Cameroon
Junior Member
Hi all,
I have been trying to write a query which will give me the CONSTRAINT NAME, CONSTRAINT TYPE, STATUS, INDEX NAMES, SEARCH CONDITION,REFERENCE TABLE, REFERENCED SCHEMA using the following tables USER_TAB_COLUMNS,USER_CONSTRAINTS,USER_CONS_COLUMNS,USER_IND_COLUMNS from the USER_TABLES. I have not been able to come out with the query.
Pls i will be happy with your help.

Thanks
Re: Constraints [message #320001 is a reply to message #320000] Tue, 13 May 2008 12:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you already tried and why it does not fit your requirements.

Regards
Michel
Re: Constraints [message #320005 is a reply to message #320001] Tue, 13 May 2008 12:28 Go to previous messageGo to next message
kimbeng
Messages: 4
Registered: March 2008
Location: Cameroon
Junior Member
This is what i have done so far

SELECT UC.CONSTRAINT_NAME, UC.CONSTRAINT_TYPE, UC.R_CONSTRAINT_NAME, 
UC.STATUS, UC.INDEX_NAME, UC.SEARCH_CONDITION,
DECODE(UC.CONSTRAINT_TYPE,'R',UIC.COLUMN_NAME, NULL) COLAREFERENCER,
DECODE(UC.CONSTRAINT_TYPE,'R',UCC.COLUMN_NAME, NULL) COLREFERENCEE, 
UCC.TABLE_NAME TABLEREFERENCEE
FROM  USER_CONSTRAINTS UC, ( SELECT UTC.TABLE_NAME, UTC.COLUMN_NAME, 
                             UTC.DATA_TYPE, UTC.DATA_LENGTH, UTC.DEFAULT_LENGTH 
                             FROM USER_TAB_COLUMNS UTC
                             WHERE UTC.TABLE_NAME 
				IN (SELECT UT.TABLE_NAME FROM USER_TABLES UT)) COL,
      USER_IND_COLUMNS UIC, USER_CONS_COLUMNS UCC 
WHERE UC.TABLE_NAME = COL.TABLE_NAME
AND   UC.TABLE_NAME = UIC.TABLE_NAME 
AND   UCC.COLUMN_NAME = UIC.COLUMN_NAME
AND   UCC.TABLE_NAME = UIC.TABLE_NAME
AND   UCC.TABLE_NAME  = UC.TABLE_NAME
AND   UCC.CONSTRAINT_NAME =  UC.R_CONSTRAINT_NAME ; 

thanks

[Updated on: Tue, 13 May 2008 12:33] by Moderator

Report message to a moderator

Re: Constraints [message #320006 is a reply to message #320005] Tue, 13 May 2008 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is not correct?

Regards
Michel

[Updated on: Tue, 13 May 2008 12:36]

Report message to a moderator

Re: Constraints [message #320007 is a reply to message #320006] Tue, 13 May 2008 12:34 Go to previous messageGo to next message
kimbeng
Messages: 4
Registered: March 2008
Location: Cameroon
Junior Member
i could not get the referenced columns and referenced table.

thanks
Re: Constraints [message #320008 is a reply to message #320007] Tue, 13 May 2008 12:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why are you using of %COL% views?

Referenced columns are not in the list of fields you want to return: CONSTRAINT NAME, CONSTRAINT TYPE, STATUS, INDEX NAMES, SEARCH CONDITION,REFERENCE TABLE, REFERENCED SCHEMA
All these ones are in user_constraints view

Regards
Michel
Re: Constraints [message #320009 is a reply to message #320006] Tue, 13 May 2008 12:40 Go to previous messageGo to next message
kimbeng
Messages: 4
Registered: March 2008
Location: Cameroon
Junior Member
i have no reason using the %COL% views. I was just trying to see if i could get the results. Pls tell me which views to use


thanks
Re: Constraints [message #320011 is a reply to message #320009] Tue, 13 May 2008 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said all the fields you asked are in USER_CONSTRAINTS.

Regards
Michel
Re: Constraints [message #320038 is a reply to message #320011] Tue, 13 May 2008 16:34 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
maybe this will get you going...
  • Attachment: t.sql
    (Size: 2.05KB, Downloaded 157 times)
Previous Topic: null field and changing data question
Next Topic: WHEN NO DATA FOUND EXCEPTION - COUNT - MAX Functions
Goto Forum:
  


Current Time: Mon Dec 05 13:00:14 CST 2016

Total time taken to generate the page: 0.05923 seconds