Tuesday, July 23, 2013

Find all tables without primarykey in Database

As a  DBA we need to make sure that all the tables in your database are have their uniquesness
 so that the rows are not duplicate and we avoid the redundant data in our databases.

 below is the simple sql that can give you list of tables that don't have any primary key:


SELECT OWNER, table_name
FROM all_tables
MINUS
SELECT OWNER,table_name
FROM all_constraints
WHERE constraint_type = 'P' AND OWNER NOT IN ('sys','system');

you list out all the schemas you want to avoid looking for.

No comments:

Post a Comment