Is there an easy query to get a list of tables that don't have any primary
key?
I've tried a couple of different ones, but none of them work quite right.
Seems like this should be easy.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mike Sardin
INET: [EMAIL PRO
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, Novembe
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 = Referential
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: Tuesda
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, 200
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'
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 B
dina [SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, November 27, 2002 6:25 AM
To: Multiple recipients of list ORACLE-L
Subject: Primary Key Constraints
Is there an easy query to get a list of tables that don't have any primary
key?
I've tried a couple of different ones, but none of t
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
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 any
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 exist
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
13 matches
Mail list logo
