10 Replies Latest reply: Aug 30, 2013 3:58 PM by CarolinC RSS

    Is there a way to get a list of tables with multi-column primary keys?

    CarolinC

      I have a model of over 1000 tables, each having primary keys and some with unique indexes.  I need a list of multi-column primary keys and a list of unique indexes that are not primary keys.  Is there are way get this information easily?

        • 1. Re: Is there a way to get a list of tables with multi-column primary keys?
          UserAL1178M

          create table test_

          (

                 id1 number,

                 id2 number,

                 name varchar2(20)

          )

           

          alter table test_ add constraint PK_tets_ primary key (id1, id2)

           

          select dd.OWNER, dd.CONSTRAINT_NAME, dd.TABLE_NAME, dd.POSITION, d.CONSTRAINT_type, dd.COLUMN_NAME from user_cons_columns dd join user_CONSTRAINTS d on dd.constraint_name = d.constraint_name

          and d.CONSTRAINT_TYPE in ('P', 'U') and d.table_name = 'TEST_'

          order by dd.table_name, CONSTRAINT_TYPE, position

           

          OWNER    CONSTRAINT_NAME    TABLE_NAME    POSITION    CONSTRAINT_TYPE    COLUMN_NAME

          HR    PK_TETS_    TEST_    1    P    ID1

          HR    PK_TETS_    TEST_    2    P    ID2

           

          You can see the position of primary key.

           

          So you can group by CONSTRAINT_NAME having count(*) > 1

           

          Hope this help

          • 2. Re: Is there a way to get a list of tables with multi-column primary keys?
            CarolinC

            Thanks for the quick reply.  I was able to get the multi-column key information from my Oracle database using a similar query. I still need an index query to get table_name and column_name where the index is unique and is not the primary key.  I can search my ddls but I should be able to query the database.  Is there no way to accomplish this in the Data Modeler?

            • 3. Re: Is there a way to get a list of tables with multi-column primary keys?
              UserAL1178M

              If i understood right you can use the same query.

               

              select dd.OWNER, dd.CONSTRAINT_NAME, dd.TABLE_NAME, dd.POSITION, d.CONSTRAINT_type, dd.COLUMN_NAME from user_cons_columns dd join user_CONSTRAINTS d on dd.constraint_name = d.constraint_name

              and d.CONSTRAINT_TYPE in ('U') and d.table_name = 'TEST_' and dd.COLUMN_NAME not in

               

              (

              select dd.COLUMN_NAME from user_cons_columns dd join user_CONSTRAINTS d on dd.constraint_name = d.constraint_name

              and d.CONSTRAINT_TYPE in ('P') and d.table_name = 'TEST_'

              )

              • 4. Re: Is there a way to get a list of tables with multi-column primary keys?
                user13430807

                Hello,

                 

                you can get this information in several ways:

                1- you can write a java script which prints out the tables with the required information. You can execute the script using 'Tool>Design rules>Transformation'. You can find several examples of these scripts in DM and on Data Modeler forum.

                2- or you export the design to the reporting database and write a query on the tables of the reporting database. The database is not documented but you can easily find the tables containing the information.

                 

                Best regards,

                Joop

                • 5. Re: Is there a way to get a list of tables with multi-column primary keys?
                  Philip Stoyanov-Oracle

                  Hi Carolin,

                   

                  Is there no way to accomplish this in the Data Modeler?

                  You need Data Modeler 4.0 to get them. And you need to get familiar with search/report functionality - resources:

                  here OLL Advanced Search

                  and here http://www.thatjeffsmith.com/

                  So in search panel:

                  1) you need to switch to advanced mode, check "use RegEx" check box

                  2) Select Index as "Object type" - in this category you'll get all indexes + PK and UK constraints

                  3) Select "usedColumns" property (available in DM 4 - this is why you need version 4) and put \n+

                  as "Search Text"

                  4) Press find and you'll get all indexes having more than one column

                   

                  There is a bug in DM 4.0 EA1 and you cannot use AND clause and more than one property, otherwise for filtering of

                  primary keys you can use either property "PK" with text true or property "Index State" with text Primary

                  After you get the result you can use "reports" button to get into reporting part - create template defining which properties (and their names) to go into report, sort order; generate report using that template.

                  You can export to excel file and easy discard indexes that are not PK.

                  In similar way (using Index State property) you can get unique indexes or unique constraints

                   

                  Philip

                  • 6. Re: Is there a way to get a list of tables with multi-column primary keys?
                    marcusafs

                    Is there any documentation on transformations?  I see examples but I am not sure how to use them or how to write my own that may need to perform different operations.  I would like to transform some of my Designer API procedures to be used in SDDM.

                    • 7. Re: Is there a way to get a list of tables with multi-column primary keys?
                      marcusafs

                      I have a generator that creates merge statements and I needed a way to retrieve PK, if none, UK and if none the first Unique Index.  This is taken from a cursor so there are some variables for schema and table name and a constant for pk 'P' and 'U'.

                      -- Primary/Unique Key
                      SELECT   *
                      FROM     dba_cons_columns concol, dba_constraints con
                      WHERE    concol.owner = UPPER ( pv_schema )
                      AND      concol.table_name = UPPER ( pv_table_name )
                      AND      ( con.constraint_type = const.v_constraint_primary
                      OR        ( con.constraint_type = const.v_constraint_unique
                      AND        NOT EXISTS
                                   (SELECT NULL
                                    FROM   dba_constraints conx
                                    WHERE  conx.constraint_type = const.v_constraint_primary
                                    AND    conx.owner = con.owner
                                    AND    conx.table_name = con.table_name
                                    AND    conx.constraint_name = con.constraint_name) ) )
                      AND      concol.owner = con.owner
                      AND      concol.table_name = con.table_name
                      AND      concol.constraint_name = con.constraint_name;
                      
                      -- Unique Index
                      SELECT *
                      FROM   dba_ind_columns indcol,
                             (SELECT   indcol.table_owner,
                                       indcol.table_name,
                                       indcol.index_owner,
                                       MIN ( indcol.index_name ) min_index_name
                              FROM     dba_ind_columns indcol, dba_indexes ind
                              WHERE    ind.index_type = 'NORMAL'
                              AND      ind.uniqueness = 'UNIQUE'
                              AND      indcol.table_owner = UPPER ( pv_schema )
                              AND      indcol.table_name = UPPER ( pv_table_name )
                              AND      indcol.table_owner = ind.table_owner
                              AND      indcol.table_name = ind.table_name
                              AND      indcol.index_owner = ind.owner
                              AND      indcol.index_owner = indcol.table_owner
                              AND      indcol.index_name = ind.index_name
                              GROUP BY indcol.table_owner,
                                       indcol.table_name,
                                       indcol.index_owner) ind
                      WHERE  indcol.table_owner = ind.table_owner
                      AND    indcol.table_name = ind.table_name
                      AND    indcol.index_owner = ind.index_owner
                      AND    indcol.index_name = ind.min_index_name;
                      
                      • 8. Re: Is there a way to get a list of tables with multi-column primary keys?
                        Kent Graziano

                        Actually the Data Modeler reporting repository is documented. Look under \datamodeler\reports\Reporting Schema diagrams. There you will find pdf schema diagrams for the repository. That table and column names are pretty straight forward you you should be able to figure out how to join them.

                         

                        If not, look at the Reports tab in SQL Developer (not modeler). There are a set of reports there (delivered) that pull data from the data modeler repository, under the Data Modeler Reports folder. If you Copy one of the existing reports (right mouse,copy) to the User Defined reports folder you can then edit the report to see the SQL which gives oyu some of the joins.

                         

                        Here is a simple report I wrote to pull a report of just the tables and table comments;

                        -- table comment report for SDDM

                        SELECT d.design_name "Design",

                          t.table_name "Table Name",

                          nvl(DMRS_LARGE_TEXT.TEXT, 'NO COMMENT RECORDED') "Database Comment"

                        FROM dmrs_tables t,

                                   dmrs_designs d,

                                   DMRS_LARGE_TEXT

                        WHERE t.OVID  = DMRS_LARGE_TEXT.OVID(+)

                              AND t.design_ovid  = d.design_ovid

                              AND DMRS_LARGE_TEXT.TYPE(+) = 'CommentsInRDBMS'

                              AND (:Design IS NULL OR upper(d.design_name) LIKE upper(:Design))

                        order by t.table_name

                         

                        Now this one can more easily be done today with the features of the Search and report tool (even in 3.3) but it should give oyu an idea of how to go about writing something more sophisticated.

                        • 9. Re: Is there a way to get a list of tables with multi-column primary keys?
                          CarolinC

                          Thank you all for your help.  I have been able to complete the task.  I am new to this forum and you all were not only knowledgeable but prompt.

                          • 10. Re: Is there a way to get a list of tables with multi-column primary keys?
                            CarolinC


                            Awesome - just what I was looking for from within SDDM!  I had v3 so downloaded v4.