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):
SELECT
sys.objects.name
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
AND 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.