THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Finding Tables Without Referential Integrity

Following up on yesterday's post from Louis, sometimes you encounter databases with missing referential integrity constraints. Even when the database is intended to have RI, accidents happen. Maybe a script dropped RI constraints before a bulk load and didn't quite get all of the constraints recreated. Whatever the reason, it's good to have a script to identify tables that are not referenced and do not reference other tables. Here's a script that works on SQL Server 2005 and 2008 (updated to no longer use sysreferences as per Adam Machanic's suggestion): 

FROM sys.objects
LEFT JOIN sys.foreign_keys referenced
ON sys.objects.object_id = referenced.referenced_object_id
LEFT JOIN sys.foreign_keys parent
ON sys.objects.object_id = parent.parent_object_id
WHERE sys.objects.type = 'U'
AND referenced.referenced_object_id IS NULL
parent.parent_object_id IS NULL

Louis did a good job explaining the importance of using the database engine to protect the integrity of the data. As a consultant, I've seen lots of databases with varying degrees of referential integrity. It never works out well in the long run when referential integrity isn't enforced in the database. Application code - even if it is christened object relational mapping - is no substitute for imposing referential integrity checks in the database. A relational database is more than a data storage repository - and it should be.


Published Wednesday, April 8, 2009 1:38 PM by John Paul Cook
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



jamiet said:

Hi John,

Very useful, thank you very much.

I wonder if  could ask a favour. Would you be willing to join my online SQL code repository and submit this snippet to it? You can read up about the repository at

Failing that, would you permit me to take the snippet and put it into the repository myself? I'll attribute it to you of course and link back here.


Jamie Thomson

April 8, 2009 3:00 PM

John Paul Cook said:

Code snippets I post here are freely redistributable with or without attribution. Attribution is appreciated as long as the code works! If I ever post code that doesn't work, please don't attribute it to me!

April 8, 2009 3:51 PM

jamiet said:

Cool, thanks John. I've added it to the repository.

April 9, 2009 4:18 AM

DavidStein said:

Pardon my ignorance but I wanted to verify this.  The list of tables that is returned by this script is the list of tables that have no referential integrity to any tables in the database?  

April 9, 2009 12:02 PM

John Paul Cook said:

Yes, David, the script returns a list of tables that neither reference other tables nor are referenced by other tables. They are neither children nor parents. Sometimes tables like these are errors caused by a dropped foreign key relationship.

April 9, 2009 1:11 PM

DavidStein said:

Well, the reason I ask is because when I run that script against my ERP Software Database, every single table shows up.  

Not a good sign.  

April 9, 2009 2:25 PM

John Paul Cook said:

It is quite common for large software packages such as ERPs and CRMs to enforce all constraints and referential integrity through code. Packages that run on all major relational database platforms are much more easy to port between different database platforms when none of the data integrity features of the database are used. As long as the data is always accessed through the application code and there aren't any bugs in the application code, there won't be any problems with the data.

April 9, 2009 3:18 PM

Madhivanan said:

John Paul Cook has posted in his blog to show the list of tables that have no Referential Integrity.

April 10, 2009 10:42 AM

drsql said:

I must need more code to get all of the comments :)  And definitely missing constraints are a big problem.  Sometimes over the course of dropping and adding them you lose a few if you aren't careful.

I use a comparison tool and my model database to compare and make sure that nothing is lost during upgrades lately, but I used to have this problem quite often.

April 11, 2009 12:15 AM

RowlandG said:

Enjoyed your post -- thanks!

July 21, 2009 8:11 AM

Leave a Comment


About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog



Privacy Statement