Identify Orphan Data "Data Integrity"

Question

Identify Orphan Data "Data Integrity"

tapaskumardm

SSC Eights!

Points: 812
More actions
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.

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

tapaskumardm SSC Eights! Points: 812 More actions · Answer 2

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

tapaskumardm SSC Eights! Points: 812 More actions · Answer 6

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.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 7

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lowell SSC Guru Points: 323518 More actions · Answer 8

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!

tapaskumardm SSC Eights! Points: 812 More actions · Answer 9

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

I will be happy to get some conclusion on this.

Thanks.

Lowell SSC Guru Points: 323518 More actions · Answer 10

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!