Tables without primary key

[Michael Maier]

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

[Mark C. Stock]

"Michael Maier" <ma***************@yahoo.de> wrote in message
news:99**************************@posting.google.c om...
| 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

[Christine]

"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message news:<9M********************@comcast.com>...

"Michael Maier" <ma***************@yahoo.de> wrote in message
news:99**************************@posting.google.c om...
| 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');

[Michael Maier]

Hi,

thx a lot, i will try it tomorrow.
Best Regards,
Micha

[Pablo R]

ma***************@yahoo.de (Michael Maier) wrote in message news:<99**************************@posting.google. 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_name from dba_tables
minus
select owner,table_name from dba_constraints
where constraint_type != 'P'

Pablo Rovedo
from Argentina