6 Replies Latest reply: Dec 19, 2013 5:36 AM by 1048939 RSS

    Generate ER Diagram from Sql Data Modeler

    1048939

      Hi,

       

      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?

       

      Thanks.

        • 1. Re: Generate ER Diagram from Sql Data Modeler
          MartijnBos

          If all these relations are enforces by primary- foreign key relllllltion ships (which they should), you can have a look at user_constraints and user_cons_columns

          With these 2 views you can get all your relations.

          • 2. Re: Generate ER Diagram from Sql Data Modeler
            BluShadow

            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).

            • 3. Re: Generate ER Diagram from Sql Data Modeler
              1048939

              Thanks, let me try it.

              • 4. Re: Generate ER Diagram from Sql Data Modeler
                1048939

                I have used the following query on the data dictionary to find the tables involved in the relationship.

                 

                SELECT DISTINCT TABLE_NAME

                FROM USER_CONSTRAINTS

                WHERE constraint_name IN

                (

                SELECT R_CONSTRAINT_NAME

                FROM USER_CONSTRAINTS

                WHERE CONSTRAINT_TYPE = 'R'

                AND OWNER = 'TRIPLEPOINT')

                ORDER BY TABLE_NAME

                 

                I think the above query gives us the desired result.

                • 5. Re: Generate ER Diagram from Sql Data Modeler
                  MartijnBos
                  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

                  • 6. Re: Generate ER Diagram from Sql Data Modeler
                    1048939

                    Thanks very much!