-- WaterOx Consulting, Inc
-- Script to identify untrusted foreign keys in a database

USE [databasename] -- change to your databasename
GO

SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
FROM    sys.foreign_keys i
        INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
        INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE   i.is_not_trusted = 1 
        AND i.is_not_for_replication = 0
        AND i.is_disabled = 0

-- Now you can use the result set from the first script to run the alter table command to re-establish trust of the FK
-- Syntax: ALTER TABLE <s.name from results>.<o.name from results> WITH CHECK CHECK CONSTRAINT <i.name from results>


-- Script 2 - Generate alter table commands for all untrusted FK constraints in a database.
-- Use this script to generate the alter table statements in the result set so they can be manually run.
-- This script can be easily modified to execute the statements generated, but ensure that you want to enable all constraints first.

SELECT  'ALTER TABLE [' + s.name + '].[' + o.name + '] WITH CHECK CHECK CONSTRAINT [' + i.name + ']' AS keyname
FROM    sys.foreign_keys i
        INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
        INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE   i.is_not_trusted = 1
        AND i.is_not_for_replication = 0
        AND i.is_disabled = 0