Change Trusted State of Foreign Keys

by jasonstrate, posted in Database Administration, T-SQL

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
T.

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.