Change Trusted State of Foreign Keys

Posted by & filed under , , , .

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

    (

    RowID int IDENTITY(1,1)

    ,SchemaName sysname

    ,TableName sysname

    ,FKeyName sysname

    )

 

DECLARE @RowID int

    ,@SchemaName sysname

    ,@TableName sysname

    ,@FKeyName sysname

    ,@SQL nvarchar(1000)

 

INSERT INTO @NonTrustedForeignKeys

    (

    SchemaName

    ,TableName

    ,FKeyName

    )

SELECT s.name

    ,t.name

    ,fk.name

FROM sys.foreign_keys fk

    INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id

    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE is_not_trusted = 1

 

WHILE EXISTS (SELECT * FROM @NonTrustedForeignKeys)

BEGIN

    BEGIN TRY

        SELECT @RowID = RowID 

            ,@SchemaName = SchemaName

            ,@TableName = TableName

            ,@FKeyName= FKeyName

        FROM @NonTrustedForeignKeys

        WHERE RowID = (SELECT MIN(RowID) FROM @NonTrustedForeignKeys)

 

        SET @SQL = 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' WITH CHECK CHECK CONSTRAINT ' + (@FKeyName)

 

        EXEC sp_ExecuteSQL @SQL

 

        DELETE FROM @NonTrustedForeignKeys

        WHERE RowID = @RowID

 

        PRINT 'COMPLETED: ' + @FKeyName

    END TRY

    BEGIN CATCH

        DBCC CHECKCONSTRAINTS(@FKeyName)

    END CATCH

END

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.