Ever want to change those foreign keys created with NOCHECK to CHECK? Not exactly an earth shattering requirement but having all of the foreign keys trusted helps keep the relationships in your databases solid. Exactly what a good foreign key should be doing.
Changing the foreign key to trusted does have the risk of uncovering data issues in the relationship. It shouldn’t really be called a risk though since it is more of an enlightening event. But in the event that changing the foreign key to trusted fails due to validation errors, the CATCH block will execute DBCC CHECKCONSTRAINTS to uncover the issues with the relationship.
Anyways, I had to dig this out and update it for a client today. I figured I’d throw it up here as well since I tend to do a good job at misplacing some of these useful scripts from time to time.
SET NOCOUNT ON
DECLARE @NonTrustedForeignKeys table
DECLARE @RowID int
INSERT INTO @NonTrustedForeignKeys
FROM sys.foreign_keys fk
INNERJOIN sys.tables t ON fk.parent_object_id = t.object_id
INNERJOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE is_not_trusted = 1
WHILEEXISTS (SELECT * FROM @NonTrustedForeignKeys)
SELECT @RowID = RowID
,@SchemaName = SchemaName
,@TableName = TableName
WHERE RowID = (SELECTMIN(RowID) FROM @NonTrustedForeignKeys)
Two last things. First, don’t just run out and run this on production during peak hours on a huge database. Second, those that follow me on Twitter – this was the thing the I “ug”ed on today. Just a case of too simple to recall.