I want to use the Oracle Sql Developer Data Modeler to generate ER diagram for my schema. There are huge number of tables in this schema, so I would like to identify only those tables which need to be selected for generating my ER diagram.
Basically, I want only those table which are having relationship with other tables here. The reason being, if I select all tables in the schema then I would get those tables in the ER diagram which don't have any relationship with other tables.
Can someone please suggest writing queries which yield this from data dictionary?
Well, your requirement is based on the the database schemas having been designed with proper primary key and foreign key constraints in place. If they're not there then the database doesn't know about the relationships between tables, and such relationships are just theoretical (and as such usually controlled by the application that uses them).
Of course there are also tables that are used by applications for lookups and other reasons, so they're part of the application and should be included on ER diagrams, even if they have no direct relationship to any one table (or they could have relationships to many tables).
So, rather than try and write queries to figure out what tables are required, why not let the Data Modeller tool generate an ER diagram from the information that IS known about on the database, and then you can see if the relationships exist, or if they're missing and need manually putting on the diagram (or applying to the database).
I have used the following query on the data dictionary to find the tables involved in the relationship.
SELECT DISTINCT TABLE_NAME
WHERE constraint_name IN
WHERE CONSTRAINT_TYPE = 'R'
AND OWNER = 'TRIPLEPOINT')
ORDER BY TABLE_NAME
I think the above query gives us the desired result.
select uc1.constraint_name, uc1.table_name, uc1.constraint_type, uc2.constraint_name, uc2.table_name, uc2.constraint_type from user_constraints uc1, user_constraints uc2 where uc1.constraint_type='R' and uc1.r_constraint_name=uc2.constraint_name
This gives you the relation between the primary and foreign key