Home » SQL & PL/SQL » SQL & PL/SQL » number of tables refering a particular column (9i)
number of tables refering a particular column [message #402733] Tue, 12 May 2009 04:22 Go to next message
mohannksr
Messages: 28
Registered: January 2009
Junior Member
hi,

i have a table

tb_address
(
seq_no number,
country varchar2(10),
city varchar2(10),
add1 varchar2(10),
add2 varchar2(10),
add3 varchar2(10),
state varchar2(10)
);

The column city is refered(foreign keyed) by multiple tables.

Is there a query

1.to get the number of tables
refering the column CITY in table tb_address...?

2.to get each table,column refering the
column CITY in table tb_address.?
Re: number of tables refering a particular column [message #402735 is a reply to message #402733] Tue, 12 May 2009 04:30 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Take a look at USER_CONS_COLUMNS and USER_CONSTRAINTS.

Obviously, once you get the result of your second question, it will be easy to count them (and get the answer of the first question).
Re: number of tables refering a particular column [message #402736 is a reply to message #402733] Tue, 12 May 2009 04:32 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@mohannksr,

Trying playing around with USER_CONSTRAINTS or ALL_CONSTRAINTS

Hope these helps.

Regards,
Jo
Re: number of tables refering a particular column [message #402737 is a reply to message #402736] Tue, 12 May 2009 04:34 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Also, a general tip regarding the Oracle data dictionary:

Have a look at the "dictionary" view.

SELECT * FROM DICTIONARY;

SELECT * FROM DICTIONARY WHERE Lower(comments) LIKE '%constrain%';
Re: number of tables refering a particular column [message #402746 is a reply to message #402737] Tue, 12 May 2009 05:30 Go to previous messageGo to next message
mohannksr
Messages: 28
Registered: January 2009
Junior Member
HI,


the following query gives the
solution for my 2nd Q.


SELECT DISTINCT USER_CONSTRAINTS.TABLE_NAME,
USER_CONSTRAINTS.CONSTRAINT_TYPE,
USER_CONS_COLUMNS.COLUMN_NAME

FROM USER_CONS_COLUMNS,
USER_CONSTRAINTS
WHERE USER_CONS_COLUMNS.COLUMN_NAME='city'
AND USER_CONSTRAINTS.CONSTRAINT_NAME=USER_CONS_COLUMNS.CONSTRAINT_NAME
AND USER_CONSTRAINTS.CONSTRAINT_TYPE='R'

i got for my 1st Q too by modifying the
above query.

Thank you all..
Re: number of tables refering a particular column [message #402778 is a reply to message #402746] Tue, 12 May 2009 07:47 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
mohannksr wrote on Tue, 12 May 2009 06:30
HI,


the following query gives the
solution for my 2nd Q.

WHERE USER_CONS_COLUMNS.COLUMN_NAME='city'



I very much doubt that.
Re: number of tables refering a particular column [message #402787 is a reply to message #402778] Tue, 12 May 2009 08:28 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
joy_division wrote on Tue, 12 May 2009 18:17
mohannksr wrote on Tue, 12 May 2009 06:30

the following query gives the
solution for my 2nd Q.

WHERE USER_CONS_COLUMNS.COLUMN_NAME='city'



I very much doubt that.



Nice catch. ./fa/1581/0/
Hope OP understood the flaw unless of course he uses double-quotes for the column in his table creation script.

Regards,
Jo
Re: number of tables refering a particular column [message #402898 is a reply to message #402787] Wed, 13 May 2009 00:43 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd rather say that the OP didn't copy/paste the original query, but typed it manually. As he doesn't type (blindly) with 10 fingers and watches the screen, CAPS LOCK was switched on inversely and thus the funny table name quoting.
Re: number of tables refering a particular column [message #403022 is a reply to message #402898] Wed, 13 May 2009 09:12 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Littlefoot wrote on Wed, 13 May 2009 01:43
I'd rather say that the OP didn't copy/paste the original query, but typed it manually. As he doesn't type (blindly) with 10 fingers and watches the screen, CAPS LOCK was switched on inversely and thus the funny table name quoting.


I didn't even think of that. I love when people try to decipher what other users are doing. The only problem is, what happened here:
AND USER_CONSTRAINTS.CONSTRAINT_TYPE='R'

Re: number of tables refering a particular column [message #403042 is a reply to message #403022] Wed, 13 May 2009 09:47 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member

Hmmm.... I was thinking joy_division pointed out that USER_CONS_COLUMNS.COLUMN_NAME won't hold data in lower case.

*Sigh* I might have misunderstood the whole point.
./fa/1637/0/
Re: number of tables refering a particular column [message #403095 is a reply to message #403042] Wed, 13 May 2009 12:57 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
no joicejohn, you were correct. That is what I was pointing out Smile

Then littlefoot said that maybe OP had the caps lock key on and when they went to use the shift key to put the column name in upper case, it actually reversed it to lower case.

And then I pointed out that if that was the case, why was the ='R' not in lower case too?
Re: number of tables refering a particular column [message #403118 is a reply to message #403095] Wed, 13 May 2009 15:55 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
He thought that he had pressed the <Shift> key, but it was <Ctrl> instead (not very likely, I admit). Or, he "forgot" to press <Shift>. Or you were right and I was wrong Smile
Re: number of tables refering a particular column [message #403119 is a reply to message #403118] Wed, 13 May 2009 16:08 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
OR, he actually posted an exact copy and paste of the code, but then gremlins got into the interweb and messed about with it Very Happy
Previous Topic: Can you let me know the difference between two anonymous blocks (merged 3)
Next Topic: Handling Rejected Rows
Goto Forum:
  


Current Time: Mon Dec 05 04:44:40 CST 2016

Total time taken to generate the page: 0.12502 seconds