Tables without primary key

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Michael Maier
    #1

    Tables without primary key

    Hi,

    for an OnlinerReorg (SAP/ORACLE) i have to know the name of the tables
    without primary key.

    Where can i find it with sqlplus or abap?

    THX

    Best Regards,
    Micha
    Tags: None
    • Mark C. Stock
      #2
      Re: Tables without primary key


      "Michael Maier" <maier_michael2 003@yahoo.dewro te in message
      news:99f00224.0 409080001.21422 93e@posting.goo gle.com...
      | Hi,
      |
      | for an OnlinerReorg (SAP/ORACLE) i have to know the name of the tables
      | without primary key.
      |
      | Where can i find it with sqlplus or abap?
      |
      | THX
      |
      | Best Regards,
      | Micha

      assuming SAP uses database constraints, these are all listed in the
      DBA_CONSTRAINTS (or USER_CONSTRAINT S) data dictionary view, CONSTRAINT_TYPE
      = 'P'

      you can write a query outer joining this to DBA_TABLES (or USER_TABLES) and
      check for NULL constraint names, or use a MINUS type query

      are you familiar with these techniques?

      ++ mcs


        Comment

        • Christine
          #3
          Re: Tables without primary key

          "Mark C. Stock" <mcstockX@Xenqu ery .comwrote in message news:<9MydnRI6Y N6LS6PcRVn-uw@comcast.com> ...
          "Michael Maier" <maier_michael2 003@yahoo.dewro te in message
          news:99f00224.0 409080001.21422 93e@posting.goo gle.com...
          | Hi,
          |
          | for an OnlinerReorg (SAP/ORACLE) i have to know the name of the tables
          | without primary key.
          |
          | Where can i find it with sqlplus or abap?
          |
          | THX
          |
          | Best Regards,
          | Micha
          >
          assuming SAP uses database constraints, these are all listed in the
          DBA_CONSTRAINTS (or USER_CONSTRAINT S) data dictionary view, CONSTRAINT_TYPE
          = 'P'
          >
          you can write a query outer joining this to DBA_TABLES (or USER_TABLES) and
          check for NULL constraint names, or use a MINUS type query
          >
          are you familiar with these techniques?
          >
          ++ mcs
          Here you are. ^_^

          select owner, table_name
          from dba_tables
          where table_name not in
          (select TABLE_NAME
          from dba_constraints
          where CONSTRAINT_TYPE ='P')
          and owner not in ('SYS', 'SYSTEM');

            Comment

            • Michael Maier
              #4
              Re: Tables without primary key

              Hi,

              thx a lot, i will try it tomorrow.


              Best Regards,
              Micha

                Comment

                • Pablo R
                  #5
                  Re: Tables without primary key

                  maier_michael20 03@yahoo.de (Michael Maier) wrote in message news:<99f00224. 0409091212.336e 2937@posting.go ogle.com>...
                  Hi,
                  >
                  thx a lot, i will try it tomorrow.
                  >
                  >
                  Best Regards,
                  Micha
                  -------------------------------------------------

                  An easier and faster way to do it would be doing the following:


                  select owner,table_nam e from dba_tables
                  minus
                  select owner,table_nam e from dba_constraints
                  where constraint_type != 'P'

                  Pablo Rovedo
                  from Argentina

                    Comment

                    Previous template Next
                    Working...