Script to List Tables With No Foreign Keys


More on Database Design Mistakes to Avoid

Related to referential integrity, in addition to systems that do not have primary keys, there are database “designs” where there were no foreign keys!  Much like a Cobol system.

Here is an Oracle script to find the tables that don’t have foreign keys.  Tables that are neither a parent table, or a child table.

I named it:  ltnofk.sql.
Meaning, list tables, with no foreign keys.

/*
Script to find all the tables with no FK.
They have no parents, and no children.
Excludes SYS and SYSTEM schemas.
By Rodger Lepinsky
*/

/*  all tables */
SELECT     D1.OWNER || ‘.’ || D1.TABLE_NAME
AS TABLE_OWNER
FROM        ALL_TABLES  D1
WHERE        D1.OWNER <> ‘SYS’
AND       D1.OWNER <> ‘SYSTEM’
GROUP BY    D1.OWNER || ‘.’ || D1.TABLE_NAME
MINUS
/*  TABLES THAT HAVE PARENTS, ARE CHILDREN  */
SELECT     A1.OWNER || ‘.’ || A1.TABLE_NAME
AS TABLE_OWNER
FROM               ALL_CONSTRAINTS A1,
ALL_CONSTRAINTS B1,
ALL_CONS_COLUMNS C1
WHERE A1.CONSTRAINT_NAME = B1.R_CONSTRAINT_NAME
AND A1.OWNER=B1.R_OWNER
AND A1.CONSTRAINT_TYPE IN (‘P’, ‘U’)
AND C1.TABLE_NAME=B1.TABLE_NAME
AND C1.CONSTRAINT_NAME = B1.CONSTRAINT_NAME
AND C1.OWNER = B1.OWNER
AND     C1.OWNER <> ‘SYS’
AND   C1.OWNER <> ‘SYSTEM’
GROUP BY   A1.OWNER || ‘.’ || A1.TABLE_NAME
MINUS
/*  TABLES THAT HAVE CHILDREN, ARE PARENTS  */
SELECT     B1.OWNER || ‘.’ || B1.TABLE_NAME
AS TABLE_OWNER
FROM       ALL_CONSTRAINTS B1,
ALL_CONS_COLUMNS C1
WHERE      B1.R_CONSTRAINT_NAME = C1.CONSTRAINT_NAME
AND                B1.R_OWNER=C1.OWNER
AND     C1.OWNER <> ‘SYS’
AND   C1.OWNER <> ‘SYSTEM’
AND                B1.CONSTRAINT_TYPE = ‘R’
GROUP BY   B1.OWNER || ‘.’ || B1.TABLE_NAME
/

Again, this will also find temporary tables, which usually are stand alone, with no FK.  If you want to filter the temp tables out, add this line to the first WHERE clause:

And tables.DURATION is not null

Sometimes you might find an error_log table, or staging tables for data loads.

Investigate away!

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: