- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Foreign Keys Table!
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-01-2006 04:56 AM
тАО08-01-2006 04:56 AM
im working with Orcale9i and im having a little problem.
i need to know all the tables which have a foreign key to an table. I find out a table with all tables, comments, columns, columns_comments ( all_tables, all_tab_comments, all_col_comments), i also find a table withh all constraints but there is no informatio about the table that constraint is refered. I need to know if there is a table with this information? Can anyone help me or guide me to find out this information, please? i will be very greatfull
Thanks in advance
Best Regards
Pinki
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-01-2006 05:32 AM
тАО08-01-2006 05:32 AM
Re: Foreign Keys Table!
SELECT DISTINCT table_name
FROM sys.dba_constraints
WHERE constraint_name LIKE 'FK_%';
PCS
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-01-2006 05:04 PM
тАО08-01-2006 05:04 PM
Re: Foreign Keys Table!
unless you are systematic in naming your foreigh key constraints as 'FK_xxx' the above query might not work.
if you only want to know the table names, the following will do it:
select table_name
from all_constraints
where constraint_name in (select R_CONSTRAINT_NAME
from all_constraints)
The following data dictionary views may also be of help:
all_constraints
all_cons_columns
if fact can be any of (all_, user_ or dba_)
hope this helps!
kind regards
yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-02-2006 01:18 AM
тАО08-02-2006 01:18 AM
Re: Foreign Keys Table!
Thanks for your attention and response..
Well i think i didnt explain well what i need.
I dont want to know foreign keys of one table. What i need is, for example: I have three tablea: Employee, Department and Institution. Employee and Department have foreign keys to Institution. I need a script that finds all the tables which have a foreign key that point to Institution.
I hope you understand....
Best Regards
Pinki
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-02-2006 02:04 AM
тАО08-02-2006 02:04 AM
Re: Foreign Keys Table!
something along the lines as produced by TOAD when clicking on the 'used by' tab?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-02-2006 02:16 AM
тАО08-02-2006 02:16 AM
Re: Foreign Keys Table!
I think this is what you want. It searchs all tables in an owner that is referenced in another table:
select dc.table_name, dc.owner
from dba_constraints dc
where dc.constraint_type = 'R'
and exists (select 1 from dba_constraints dc2
where dc2.constraint_name = dc.r_constraint_name
and dc2.constraint_type = 'P')
and dc.owner =
Best Regards,
Eric Antunes
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-02-2006 02:39 AM
тАО08-02-2006 02:39 AM
Re: Foreign Keys Table!
I think:
select a.table_name, b.table_name
from all_constraints a, all_constraints b
where a.constraint_name = b.R_CONSTRAINT_NAME
and a.owner='
order by 1,2;
should work.
It shows the table and any table that reference it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-02-2006 04:19 PM
тАО08-02-2006 04:19 PM
Solutionto add to above reply, here is the SQL that you should run:
select a.table_name, b.table_name"is being referenced by"
from all_constraints a, all_constraints b
where a.constraint_name = b.R_CONSTRAINT_NAME
and a.owner='&Table_Owner'
and a.table_name='INSTITUTION'
You will be prompted to enter the owner of the table INSTITUTION
hope this helps!
kind regards
yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-02-2006 09:10 PM
тАО08-02-2006 09:10 PM
Re: Foreign Keys Table!
Thanks a lot. It works perfectly:
select a.table_name, b.table_name"
from all_constraints a, all_constraints b
where a.constraint_name=b.R_CONSTRAINT_NAME
and a.owner='&Table_Owner'
and a.table_name='INSTITUTION';
Thanks once again.
Best Regards!
Pinki