USP_CLEANUNTRUSTEDFOREIGNKEYS

CREATE procedure dbo.USP_CLEANUNTRUSTEDFOREIGNKEYS
AS
BEGIN

--create a temporary table of all the not trusted foreign keys

create table #FK_LIST
(
Table_Name nvarchar(255) collate DATABASE_DEFAULT,
FK_Name nvarchar(255) collate DATABASE_DEFAULT
);

--fill the table

insert into #FK_LIST 
select object_name(parent_object_id), name
from sys.foreign_keys
where is_not_trusted = 1;

-- create variables for the cursor

declare @TableName nvarchar(255);
declare @FKName nvarchar(255);

--create a variable for the sql

declare @alterSql as nvarchar(255);

--create a cursor of the foreign keys

declare myCursor cursor
for select Table_Name, FK_Name
from #FK_LIST;

/* optional clause to exclude certain foreign keys
where FK_NAME IN
(
'FK_Audits_AuditCodes',
'FK_Audits_ObjectTypes'
);
*/

open myCursor;
fetch next from myCursor into @TableName, @FKName;

while @@FETCH_STATUS = 0

-- Try to add "trust" the foreign key

begin
    begin try
        set @alterSql = N'alter table ' + @TableName + ' with check check constraint ' + @FKName;
        exec sp_executesql @alterSql;
        print @alterSql;
    end try

    -- if an error display a message and move on to the next

    begin catch
    print error_message();
    print 'Error occurred executing ' + @alterSql;
    end catch

    -- get the next foreign key

    fetch next from mycursor into @TableName, @FKName;
end

close myCursor;
deallocate myCursor;

END