Re: Tables without primary key
Date: 8 Sep 2004 18:51:52 -0700
Message-ID: <693b7163.0409081751.2d8068_at_posting.google.com>
"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:<9MydnRI6YN6LS6PcRVn-uw_at_comcast.com>...
> "Michael Maier" <maier_michael2003_at_yahoo.de> wrote in message
> news:99f00224.0409080001.2142293e_at_posting.google.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_CONSTRAINTS) 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');
Received on Thu Sep 09 2004 - 03:51:52 CEST