SQL Server Performance

Find Orphan Records

Discussion in 'General Developer Questions' started by cejar, Dec 15, 2003.

  1. cejar New Member

    Does anyone have any SQL scripts to find all Orphan records in a givin table?

  2. Luis Martin Moderator

  3. satya Moderator

    Use the DBCC CHECKCONSTRAINTS command in SQL Server Query Analyser on the database in question. This will check all of the tables in the current database for conflicts and child records that do not have matching parent records (ie Foreign Key Constraints).

    Satya SKJ
  4. Luis Martin Moderator

    I just check last url. No longer active, sorry.

    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  5. satya Moderator

    The post you'd referred is available, I can access it.

    Satya SKJ
  6. Luis Martin Moderator

    Yes, but when you donwload files, there is a txt informing that script are not available.

    Here is:

    Thank you for your interest in DBAction.

    The DBAction routines are distributed as custom software development.

    For more information, contact dbaction@wingenious.com.

    Several of the DBAction routines were initially offered as a free download. They are still described as such on the web site. Unfortunately, those routines are no longer free. I apologize for the misleading information on the web site. I hope to get it updated soon.

    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  7. Jon M Member

    How about using 'OUTER JOINs'? Make your query in such a way you'll be able to select or retrieve those unmatch records (with NULL).

    Jon M
  8. cejar New Member

    I once read an article in SQL Mag reader to reader where a DBA shared some code to perform this action. My subcription ran out maybe some one can that has in active subcription can find it. I forgot which issue it was in.

  9. ChrisFretwell New Member

    You can also write a few 'not exists' queries to test for orphans.

    select t1.column1, t1.column2...
    from table1 t1
    where not exists (select * from table2 t2 where t1.parentid = t2.id)

    Exists is usually 'faster' than outer joins.

  10. cejar New Member

    I'm working on getting a script together. The problem is I'm dealing with multiple servers with dozens of Dbs and Tables.

  11. ChrisFretwell New Member

    I'm assuming you have no/little enforced data integerity, or there would be no orphans. So, are your keys well named? ie can you by name, indentify parents/children? If they are, then you can use the sysobjects, syscolumns tables to generate the checks.

    If not, well, its probably manual.

  12. cejar New Member

    There is not enforced data integrity, Keys are poorly named, missing indexes like crasy, no datamodels nor documentation, but who's complaining.

  13. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by cejar</i><br /><br />There is not enforced data integrity, Keys are poorly named, missing indexes like crasy, no datamodels nor documentation, but who's complaining.<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />] any chance you can meet the original designer? [B)][B)]<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  14. ChrisFretwell New Member

    Welcome to the real world! Unfortunately, even if there was a magic tool out there, with a database like that (and many are), it wouldnt be able to figure out the keys either. Its ironic in a way, that the databases that tools work best with are usually the well designed ones that dont need the tools!<br /><br />But, you can still semi-automate it. And I say semi, because it will be work, but its reusable and may help.<br />This is a rough idea, and I'm typing while I think, so expect mistakes....<br /><br />Create RelTable<br />(Relid int identity(1,1),<br /> ParentTable varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> not null,<br /> ParentKeyColumn int not null,<br /> ChildTable int not null,<br /> ChildFKColumn int not null,<br /> Reltype int<br />)<br /><br />Then, and this is the biggest manual part....but its one time<br />insert into the table, the table and column for each parent/child combination. Use reltype for your own use, for things like 1=one-to-one, 2=one-many etc. <br /><br />While not great, this gives you relationships, and you can join to itselt to get nested relationships too. And use it for basic reporting.<br /><br />Then you write a proc to generate your not-exists queries<br /><br />so<br /><br />select 'Select ' + childtable + '.* from ' + childtable + ' where not exists (select * from ' + parenttable + ' where '+ parentkeycolumn+ '=' + childfkcolumn+ ')'<br /><br />Then save the code and run it. <br /><br />Like I said, typing while thinking, so forgive typing, spelling etc errors.<br /><br />chris
  15. cejar New Member

    Actually the Developers are in the next office from mine.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br />Thanks!!!<br />Cheers!
  16. cejar New Member

    Here is a Stored Proc I found that works for 6.5 havnet tested yet with 2000

    While cleaning up data you may create orphan records (before you have created FK constraints). This script purges orphan records from a child table. It takes the arguments of parent_table, child_table and linking_field and then finds all the child records with no parent record and deletes them.

    For SQL Server 6.5

    Author: Tom De Lancey

    if exists (select * from sysobjects where id = object_id('dbo.sp_deleteOrphans') and sysstat & 0xf = 4)
    drop procedure dbo.sp_deleteOrphans

    Create Procedure sp_deleteOrphans
    @PrimeTable varchar(30),
    @ChildTable varchar(30),
    @LinkField int
    Begin Tran
    Exec ('Delete from ' + @ChildTable +
    ' Where not exists
    (select * from '+ @PrimeTable +' where ' +
    @PrimeTable + '.' + @Linkfield + '=' +
    @ChildTable + '.' + @LinkField +')')
    If @@error <>0
    commit tran

    return (0)


Share This Page