Looking for Orphan Rows

This page has moved.  Please update your links:
http://psst0101.digitaleagle.net/2009/04/03/looking-for-orphan-rows/

Here is some SQL that you might find helpful:

SELECT ‘SELECT ”’ + C.RECNAME + ”’ AS RECNAME, OPRID FROM ‘ + D.TABLENAME +
‘ A WHERE NOT EXISTS (SELECT ”X” FROM PSOPRDEFN B WHERE A.OPRID = B.OPRID) UNION ‘
FROM PSRECFIELDALL C,
(SELECT RECNAME, SQLTABLENAME AS TABLENAME
FROM PSRECDEFN
WHERE SQLTABLENAME <> ‘ ‘
AND RECTYPE = 0
UNION
SELECT RECNAME, ‘PS_’ + RECNAME AS TABLENAME
FROM PSRECDEFN
WHERE SQLTABLENAME = ‘ ‘
AND RECTYPE = 0
) D
WHERE C.RECNAME = D.RECNAME
AND C.FIELDNAME = ‘OPRID’

This Query writes a huge SQL Statement that looks for rows in any table that has an OPRID that does not exist in the PSOPRDEFN table.

Here is how to use it:

  • Run the SQL Statement
  • Copy all of the rows as a new query
  • Remove the last Union from the last line
  • You might want to add “ORDER BY 1” at the end to get it in record order
  • Execute the newly generated query

Note, this pattern can be replicated for other fields or situations too.  It is not just limited to use with OPRID.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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: