Hi Mike,
I presume you want to disregard System tables, so here goes
select owner, table_name from dba_tables
where owner not in ('SYS','SYSTEM','OUTLN','DBSNMP')
minus
select owner, table_name from dba_constraints where constraint_type = 'P';
-Original Message-
Sent: Tuesday,
Mike,
How about:
select table_name from user_tables a
where not exists(select 1 from user_constraints b
where a.table_name = b.table_name
and b.constraint_type = 'P');
Constraint_type values are:
P = Primary Key
C = Check Constraint
R =
select owner, table_name from dba_tables where (owner, table_name) not in
(select owner, table_name from dba_constraints where constraint_type = 'P');
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday,
SQL select table_name
2 from dba_tables
3 minus
4 select table_name
5 from dba_constraints
6 where constraint_type = 'R';
This should work (does in my 8.1.7 test db).
What methods have you tried? and what was not working?
-Original Message-
Sent: Tuesday, November 26,
select owner, table_name from dba_tables where not exists (select 'a'
from dba_constraints where constraint_type = 'P'
and owner = dba_tables.owner and table_name = dba_tables.table_name)
order by owner, table_name;
Mike Sardina wrote:
Is there an easy query to get a list of tables that don't
Title: RE: Primary Key Constraints
SELECT owner, table_name
FROM DBA_TABLES t
WHERE NOT EXISTS
(SELECT 'X'
FROM DBA_CONSTRAINTS c
WHERE c.owner = t.owner
AND c.table_name = t.table_name
AND c.constraint_type = 'P')
ORDER BY 1,2
Take off the ORDER BY to speed things up
select owner, table_name from all_tables A where not exists
(select owner, table_name from all_constraints B where b.owner=A.owner and
b.table_name=A.table_name
and b.constraint_type='P')
That ought to do it. Cheers :
Ferenc Mantfeld
-Original Message-
From: Mike Sardina
how about select table_name from user_tables where table_name not
in (select table_name from user_constraints where constraint_type =
'P'); [EMAIL PROTECTED] 11/26/02 01:25PM
Is there an easy query to get a list of tables that don't have
any primarykey? I've tried a couple of different ones,
try this:
select table_name from user_tables
minus
select table_name from user_constraints where constraint_type = 'P';
-Original Message-
Sent: Tuesday, November 26, 2002 2:25 PM
To: Multiple recipients of list ORACLE-L
Is there an easy query to get a list of tables that don't have
it's sure easy enough
SELECT (OWNER, TABLE_NAME)
FROM DBA_TABLES
WHERE (OWNER, TABLE_NAME) NOT IN
(SELECT OWNER, TABLE_NAME
FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P')
HTH
Arup Nanda
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday,
Another way :
select t.owner, t.table_name
from dba_tables t
minus
select c.owner, c.table_name
from dba_constraints c
where c.constraint_type='P'
;
--- Mercadante, Thomas F [EMAIL PROTECTED]
a écrit : Mike,
How about:
select table_name from user_tables a
where not exists(select
select table_name from user_tables
minus
select table_name from user_constraints where constraint_type='P';
HTH, Krishna
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, November 26, 2002 11:25 AM
Is there an easy query to get a list of
12 matches
Mail list logo
