November 12, 2008 at 2:15 pm
I know there is an sp_something that checks the referential integrity of your tables but I can't remember what it was.
I know "sp_helpconstraint " will give me the PK/FK constraints on the table but I need to make sure data integrity.
Thanks
November 12, 2008 at 2:57 pm
I don't know of a Stored Procedure but DBCC CheckTable should do most of what you want. Ckeck it out in BOL
November 12, 2008 at 4:03 pm
Are you saying physical structures or logically if you have things set up in RI?
The constraints are enforced, as are unique indexes if you didn't allow dups when you built them, but there isn't anything that is proper integrity. You set up the level of integrity you think is appropriate.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 12, 2008 at 4:14 pm
hmm...
OK, let me rephrase the question. How do I check for "Orphaned" records?
November 12, 2008 at 4:35 pm
Hi Warren,
Checking for "Orphaned Records" can be a bear depending on how your schema (including referential integrity) is set up.
Basically, you need to test each foreign key in the child table to see if it exists in the appropriate parent table. So depending on the nature of the Key that may be more or less complex. I have a legacy table in which a predecessor set up a "selector" column and a generic foreign key column so any test requires you to first look at the selector to determine which table is the parent for the key. Ugggh.
I'm not the sharpest T-SQL guy but I ususally use Temp Tables to do this type of check. First load the temp table with the Foreign Keys, then delete from the Temp table where the Foreign Key is in the parent table. Anything left is "orphan".
hope this helps.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply