Fix Untrusted Foreign keys

Going off of Brent Ozar's note on fix untrusted foreing keys here:
http://www.brentozar.com/blitz/foreign-key-trusted/
He explains how the optimizer cannot use untrusted Fk's to generate query plan and hence can lead to suboptimals plans getting selected and hence slowness.
we decided to work on untrusted foriegn key contraints in our system to get performance improvements if any. With a system of hundreds of dbs below is the query I came up with. For finding untrusted fks and also the count of records that did not satisfy the FK constraint (if any i.e. values in child table which were not in parent table). For those the records needed to be fixed before fixing the untrusted FK's.

This was run on all dbs at once using registered server and results copied to excel


CREATE TABLE #temp
    (
      servername NVARCHAR(500) ,
      dbname NVARCHAR(500) ,
      parenttable NVARCHAR(500) ,
      RefTable NVARCHAR(500) ,
      FKName SYSNAME ,
      keyname NVARCHAR(1000) ,
      PTForeignKeyColumn NVARCHAR(500) ,
      RTForeignKeyColumn NVARCHAR(500) ,
      cntStmt NVARCHAR(MAX) ,
      cntnotexistsRecords INT NULL
    )

--select top 1 * from sys.foreign_key_columns
EXEC sp_msforeachdb N' USE [?]
INSERT INTO #temp(servername,dbname,parenttable,RefTable,FKName,keyname,PTForeignKeyColumn,RTForeignKeyColumn,cntStmt,cntnotexistsRecords)
SELECT DISTINCT @@servername AS servername, DB_NAME() AS dbname,OBJECT_NAME(i.parent_object_id) AS parenttable,OBJECT_NAME(i.referenced_object_id) AS RefTable,i.name AS FKName,
''ALTER TABLE ['' + s.name + ''].['' + o.name + ''] WITH CHECK CHECK CONSTRAINT ['' + i.name + '']'' AS keyname,
(SELECT
    c.name + '',''
FROM
    sys.columns c where fkc.parent_object_id = c.object_id AND c.column_id IN (select fkc2.parent_column_id FROM sys.foreign_key_columns fkc2 where i.parent_object_id = fkc2.parent_object_id AND i.referenced_object_id=fkc2.referenced_object_id and i.object_id=fkc2.constraint_object_id)
                                 FOR XML PATH('''')) AS PTForeignKeyColumn,
(SELECT
    c.name + '',''
FROM
    sys.columns c where fkc.referenced_object_id = c.object_id AND c.column_id IN (select fkc2.referenced_column_id FROM sys.foreign_key_columns fkc2 where i.parent_object_id = fkc2.parent_object_id AND i.referenced_object_id=fkc2.referenced_object_id)
                                 FOR XML PATH('''')  ) AS RTForeignKeyColumn,
'''' AS cntStmt,NULL AS cntnotexistsRecords
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
left JOIN sys.foreign_key_columns fkc ON i.parent_object_id = fkc.parent_object_id AND i.referenced_object_id=fkc.referenced_object_id
 WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0
and i.is_disabled = 0
AND DB_NAME() not in (''master'',''msdb'',''tempdb'',''dfwr_th'',''model'',''mmshare'',''distribution'')
;'


/** Create untrusted-mismatched records count statement **/
UPDATE  t
SET     cntStmt = 'Select @cnt_OUT = COUNT(DISTINCT '
        + REPLACE(t.PTForeignKeyColumn, ',', '') + ') FROM  [' + t.dbname
        + '].[dbo].[' + t.parenttable + '] WHERE '
        + REPLACE(t.PTForeignKeyColumn, ',', '') + ' NOT IN (SELECT '
        + REPLACE(t.RTForeignKeyColumn, ',', '') + ' FROM [' + t.dbname
        + '].[dbo].' + t.RefTable + ')'
FROM    #temp t
WHERE   ( LEN(t.PTForeignKeyColumn) - LEN(REPLACE(t.PTForeignKeyColumn, ',',
                                                  '')) ) = 1


/* CURSOR through to execute statement to get mismatched/untrusted record counts */
DECLARE @dbName SYSNAME ,
    @parentbl SYSNAME ,
    @reftbl SYSNAME ,
    @fkname SYSNAME ,
    @sqlStmt NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(200)


DECLARE cntcursor CURSOR
FOR
    SELECT  dbname ,
            parenttable ,
            reftable ,
            fkname ,
            cntstmt
    FROM    #temp
OPEN cntcursor
FETCH NEXT FROM cntcursor
INTO @dbName, @parentbl, @reftbl, @fkname, @sqlStmt

WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @cnt INT

        SET @ParmDefinition = N'@cnt_OUT int OUTPUT'

        EXECUTE sp_executesql @sqlstmt, @ParmDefinition,
            @cnt_OUT = @cnt OUTPUT;



        UPDATE  #temp
        SET     cntnotexistsRecords = @cnt
        WHERE   dbname = @dbName
                AND parenttable = @parentbl
                AND RefTable = @reftbl
                AND FKName = @fkname

        SET @cnt = 0

        FETCH NEXT FROM cntcursor
INTO @dbName, @parentbl, @reftbl, @fkname, @sqlStmt
    END

CLOSE cntcursor
DEALLOCATE cntcursor

SELECT  *
FROM    #temp
ORDER BY dbname
DROP TABLE #temp

/*
while doing the last select * from #tempdb, you can do a where cntnotexistsRecords=0 (to get only the records which satisfy the FK's) and cntStmt != NULL ( to get values where FK relationship do not involve more than one column, because I have refrained from creating a count statement for FK's involving multiple columns) This query is designed for Fk's with only one column
*/