CODEWITHSUNDEEP
oracle-database
Nidheesh
Nidheesh

Reputation: 4562

List of table which don't have a primary key

Need to get the list of tables name from a schema which doesn't have a primary key. I tried the following query to get this, but it will list all other keys except primary keys. 

SELECT a.constraint_name,a.table_name
FROM ALL_CONS_COLUMNS A
JOIN ALL_CONSTRAINTS C
ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE not in('P')
and a.owner ='my_schema';

Upvotes: 1

Views: 4017

Answers (4)

Dinesh vishe
Dinesh vishe

Reputation: 3598

Select tables.owner || ‘.’ || tables.table_name as table_owner From all_tables tables, (Select owner, TABLE_NAME, constraint_type, CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_TYPE = ‘P’ /* list of all tables with PK */ ) constr Where tables.owner = constr.owner (+) And tables.table_name = constr.table_name (+) and tables.owner <> ‘SYS’ and tables.owner <> ‘SYSTEM’ And constr.owner IS NULL And constr.table_name IS NULL ORDER BY 1

Upvotes: 0

Chris Hunt
Chris Hunt

Reputation: 111

Another way:

select owner,table_name
from all_tables
where owner = 'my_schema' 
MINUS
select owner,table_name
from all_constraints
where owner = 'my_schema' 
and constraint_type = 'P'

Upvotes: 1

Dba
Dba

Reputation: 6639

Try like this,

SELECT table_name 
FROM   all_tables A 
WHERE  table_name NOT IN 
     (
     SELECT table_name 
     FROM   all_constraints WHERE constraint_type ='P' 
     )
AND   a.owner = 'my_schema';

Upvotes: 0

user330315
user330315

Reputation:

If you only want this for the current user, it's better to user the user_xxx views instead of the all_xxx views.

The following should do what you want:

select ut.table_name
from user_tables ut
where not exists (select 1
                  from user_constraints ac
                  where ac.table_name = ut.table_name
                    and ac.constraint_type = 'P'
                  );

If you do need this for a different user, then you can use the following.

select at.*
from all_tables at
where not exists (select 1
                  from all_constraints ac
                  where ac.owner = at.owner
                    and ac.table_name = at.table_name
                    and ac.constraint_type = 'P'
                  )
and at.owner = 'MY_SCHEMA';

Don't forget that Oracle is case sensitive and that user names are stored in uppercase, so a.owner ='my_schema' will most probably not return anything.

Upvotes: 3

Related Questions