Post reply

Identify Orphan Data "Data Integrity"

  • July 17, 2012 at 2:50 am

    #272581

    As Part of Data Integrity,

    I need to Identify Orphan Data in a Database (SQL Server 2000/20005) in regular manner on production servers.

    So, Do somebody have a Predefined script to Identify this.

    or, have idea to find the Orphan data.

  • July 17, 2012 at 3:04 am

    #1513459

    Orphaned data is normally considered children records with no parent records. Easiest way to identify them is to query the child table using a left outer join to the parent table and select all records where the parent key values are null.

  • July 17, 2012 at 3:14 am

    #1513461

    Thanks,

    But I have 100's of tables in each Databases. Given way is too difficult to find those data.

    Anybody has a better way to do this.

    Thanks in advance.

  • July 17, 2012 at 3:32 am

    #1513465

    As far as I know there is no magic bullet to determine what records are orphaned. You are going to have to do the work as you are the one with access to your database, the DDL for the tables, and an understanding of the data to know what the relationships are between the data in the tables.

  • July 17, 2012 at 3:41 am

    #1513469

    I'm going to assume you don't have foreign keys in place, because if you did SQL would prevent orphaned data. If there are no foreign keys they there's no automatic way to check for potentially orphaned data as SQL has no idea that there's any relationship there at all. Hence you're going to have to sit and write queries to check.

    Consider adding foreign keys once you have your data integrity problems fixed as that will prevent such problems in the future.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 17, 2012 at 3:52 am

    #1513478

    And remember, foreign keys don't just act as a mechanism for preventing orphaned users. They also enhance performance (as long as they're enforced).

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • July 17, 2012 at 5:14 am

    #1513512

    So, You mean to say ,, Need to check each table in the Database.

    Yes , I do have foreign keys.

    any way ,,, i have to do it

    Thanks.

  • July 17, 2012 at 5:42 am

    #1513520

    The only way you can have foreign key constraints in place and still have orphaned records is for the foreign keys to have been added using the NOCHECK option after the data was already in place. This is a danger-prone practice, plus it makes it so the optimizer can't rely on the foreign keys. In addition to cleaning up your data issues, I'd suggest fixing whatever process is leading to this problem.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • July 17, 2012 at 5:46 am

    #1513524

    well, you say you have foreign keys in place, so I'd start with any contraints that are disabled or not trusted:

    SELECT

    name,

    is_disabled,

    is_not_trusted

    FROM sys.foreign_keys

    WHERE is_disabled = 1

    OR is_not_trusted = 1

    from there, i'd check if each of the tables involved with DBCC:

    DBCC CHECKCONSTRAINTS ('Suspected_Table')

    from there you can dig in to recreate any missing parents or delete /nullify the child records, as appropriate for the business.

    after that, you would want to re-enable the foreign keys to prevent it form happening again, and maybe create a DDL trigger to prevent anyone from disabling foreign keys in the future.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 17, 2012 at 6:34 am

    #1513551

    Can you put some more light on above explanation....

    I will be happy to get some conclusion on this.

    Thanks.

  • July 17, 2012 at 6:41 am

    #1513556

    tapaskumardm (7/17/2012)

    Can you put some more light on above explanation....

    I will be happy to get some conclusion on this.

    Thanks.

    not sure what you need, sorry.

    run the first query. does it return any results at all?

    if it doesn't, there cannot be orphaned data due to the disabling of foreign keys, but there could be orphaned data if someone dropped foreign keys...that requires a different investigation path.

    report back any results so we can guide you a little better.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply