SDU Tools: List and Retrust Untrusted Foreign Key Constraints in SQL Server

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. I do a lot of reviewing of database designs and one thing I always check for is foreign key constraints that are not currently trusted. So we added a tool that can help to find these. It’s called ListUntrustedForeignKeys.

The procedure takes three parameters.

@DatabaseName is the database to process @SchemasToList is a comma-delimited list of schema names to process @TablesToList is a comma-delimited list of table names to process

For the two list parameters, the value ALL can be supplied instead.

Untrusted Foreign Key Constraints

Foreign key constraints are used to check the values in database columns., to make sure they match keys (or unique values) in other tables.

What can happen though, is that the constraints are disabled for a period of time by using WITH NOCHECK, then they are re-enabled WITH CHECK. But the question is about what that means for the data that has been modified in the meantime.

Unless all the data was rechecked, SQL Server can’t depend on the values being valid.

Retrust Foreign Keys

To make it easier to recheck/retrust foreign keys, we’ve also added a second tool called RetrustForeignKeys.

The procedure also takes three parameters.

@DatabaseName is the database to process @SchemasToList is a comma-delimited list of schema names to process @TablesToList is a comma-delimited list of table names to process

For the two list parameters, the value ALL can be supplied instead.

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below. The Azure SQL DB version that only operates on the current database is simpler and can be seen by downloading the tools.

CREATE PROCEDURE SDU_Tools.ListUntrustedForeignKeys
@DatabaseName sysname,
@SchemasToInclude nvarchar(max) = N'ALL',  -- N'ALL' for all
@TablesToInclude nvarchar(max) = N'ALL'    -- N'ALL' for all
AS
BEGIN

-- Function:      List Untrusted ForeignKeys
-- Parameters:    @DatabaseName sysname         -> Database to process
-- @SchemasToInclude nvarchar(max)  -> 'ALL' or comma-delimited list of schemas to list
-- @TablesToInclude nvarchar(max)   -> 'ALL' or comma-delimited list of tables to list
-- Action:        Finds foreign keys that are not trusted
-- Return:        DatabaseName, SchemaName, TableName, ForeignKeyName
-- Refer to this video: https://youtu.be/d2-8ZMeaB0A
--
-- Test examples: 
/*

USE WideWorldImporters;
GO

ALTER TABLE Sales.Invoices 
NOCHECK CONSTRAINT FK_Sales_Invoices_Application_People;
GO

ALTER TABLE Sales.Invoices 
WITH NOCHECK CHECK CONSTRAINT FK_Sales_Invoices_Application_People;
GO

USE DATABASE_NAME_HERE;
GO

EXEC SDU_Tools.ListUntrustedForeignKeys 
     @DatabaseName = N'WideWorldImporters',
     @SchemasToInclude = N'ALL', 
     @TablesToInclude = N'ALL'; 
GO

EXEC SDU_Tools.RetrustForeignKeys 
     @DatabaseName = N'WideWorldImporters',
     @SchemasToInclude = N'ALL', 
     @TablesToInclude = N'ALL'; 
GO

EXEC SDU_Tools.ListUntrustedForeignKeys 
     @DatabaseName = N'WideWorldImporters',
     @SchemasToInclude = N'ALL', 
     @TablesToInclude = N'ALL'; 
GO

*/
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    
    DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    DECLARE @SchemaName sysname;
    DECLARE @TableName sysname;
    DECLARE @ForeignKeyName sysname;

    DECLARE @SQL nvarchar(max) = 
'WITH UntrustedForeignKeys
AS
(
       SELECT ss.[name] AS SourceSchemaName, 
              st.[name] AS SourceTableName, 
              fk.[name] AS ForeignKeyName
       FROM ' + QUOTENAME(@DatabaseName) + N'.sys.foreign_keys AS fk
       INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.tables AS st 
       ON st.object_id = fk.parent_object_id
       INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS ss
       ON ss.schema_id = st.schema_id
       WHERE st.is_ms_shipped = 0
       AND st.[name] <> N''sysdiagrams''
       AND fk.is_not_trusted <> 0 
       AND fk.is_disabled = 0
)
SELECT N''' + @DatabaseName + N''' AS DatabaseName,
       ufk.SourceSchemaName AS SchemaName, 
       ufk.SourceTableName AS TableName, 
       ufk.ForeignKeyName AS ForeignKeyName
FROM UntrustedForeignKeys AS ufk 
WHERE 1 = 1 '
    + CASE WHEN @SchemasToInclude = N'ALL' 
           THEN N''
           ELSE N'    AND ufk.SourceSchemaName IN (SELECT StringValue COLLATE DATABASE_DEFAULT FROM SDU_Tools.SplitDelimitedString('''
                + @SchemasToInclude + ''', N'','', 1))'
      END + @CRLF 
    + CASE WHEN @TablesToInclude = N'ALL' 
           THEN N''
           ELSE N'    AND ufk.SourceTableName IN (SELECT StringValue COLLATE DATABASE_DEFAULT FROM SDU_Tools.SplitDelimitedString('''
                + @TablesToInclude + ''', N'','', 1))'
      END + @CRLF + N'
ORDER BY ufk.SourceSchemaName, ufk.SourceTableName, ufk.ForeignKeyName;';
    EXEC (@SQL);
END;
GO

CREATE PROCEDURE [SDU_Tools].[RetrustForeignKeys]
@DatabaseName sysname,
@SchemasToInclude nvarchar(max) = N'ALL',  -- N'ALL' for all
@TablesToInclude nvarchar(max) = N'ALL'    -- N'ALL' for all
AS
BEGIN

-- Function:      RetrustForeignKeys
-- Parameters:    @DatabaseName sysname         -> Database to process
-- @SchemasToInclude nvarchar(max)  -> 'ALL' or comma-delimited list of schemas to list
-- @TablesToInclude nvarchar(max)   -> 'ALL' or comma-delimited list of tables to list
-- Action:        Tries to retrust untrusted foreign keys. Ignores disabled foreign keys.
-- Return:        Nil
-- Refer to this video: https://youtu.be/UM1NFlu4z28
--
-- Test examples: 
/*

USE WideWorldImporters;
GO

ALTER TABLE Sales.Invoices 
NOCHECK CONSTRAINT FK_Sales_Invoices_Application_People;
GO

ALTER TABLE Sales.Invoices 
WITH NOCHECK CHECK CONSTRAINT FK_Sales_Invoices_Application_People;
GO

SELECT [name], is_disabled, is_not_trusted 
FROM sys.foreign_keys 
WHERE OBJECT_NAME(parent_object_id) = N'Invoices';
GO

USE DATABASE_NAME_HERE;
GO

EXEC SDU_Tools.RetrustForeignKeys 
     @DatabaseName = N'WideWorldImporters',
     @SchemasToInclude = N'ALL', 
     @TablesToInclude = N'ALL'; 
GO

USE WideWorldImporters;
GO

SELECT [name], is_disabled, is_not_trusted 
FROM sys.foreign_keys 
WHERE OBJECT_NAME(parent_object_id) = N'Invoices';
GO

*/
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    
    DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    DECLARE @UntrustedForeignKeys TABLE
    (
        UntrustedForeignKeyID int IDENTITY(1,1) PRIMARY KEY,
        SchemaName sysname,
        TableName sysname,
        ForeignKeyName sysname
    );
    DECLARE @Counter int;
    DECLARE @SchemaName sysname;
    DECLARE @TableName sysname;
    DECLARE @ForeignKeyName sysname;

    DECLARE @SQL nvarchar(max) = 
'WITH UntrustedForeignKeys
AS
(
       SELECT ss.[name] AS SourceSchemaName, 
              st.[name] AS SourceTableName, 
              fk.[name] AS ForeignKeyName
       FROM ' + QUOTENAME(@DatabaseName) + N'.sys.foreign_keys AS fk
       INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.tables AS st 
       ON st.object_id = fk.parent_object_id
       INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS ss
       ON ss.schema_id = st.schema_id
       WHERE st.is_ms_shipped = 0
       AND st.[name] <> N''sysdiagrams''
       AND fk.is_not_trusted <> 0 
       AND fk.is_disabled = 0
)
SELECT ufk.SourceSchemaName, ufk.SourceTableName, ufk.ForeignKeyName
FROM UntrustedForeignKeys AS ufk 
WHERE 1 = 1 '
    + CASE WHEN @SchemasToInclude = N'ALL' 
           THEN N''
           ELSE N'    AND ufk.SourceSchemaName IN (SELECT StringValue COLLATE DATABASE_DEFAULT FROM SDU_Tools.SplitDelimitedString('''
                + @SchemasToInclude + ''', N'','', 1))'
      END + @CRLF 
    + CASE WHEN @TablesToInclude = N'ALL' 
           THEN N''
           ELSE N'    AND ufk.SourceTableName IN (SELECT StringValue COLLATE DATABASE_DEFAULT FROM SDU_Tools.SplitDelimitedString('''
                + @TablesToInclude + ''', N'','', 1))'
      END + @CRLF + N'
ORDER BY ufk.SourceSchemaName, ufk.SourceTableName, ufk.ForeignKeyName;';
    INSERT @UntrustedForeignKeys (SchemaName, TableName, ForeignKeyName)
    EXEC (@SQL);

    SET @Counter = 1;
    WHILE @Counter <= (SELECT MAX(UntrustedForeignKeyID) FROM @UntrustedForeignKeys)
    BEGIN
        SELECT @SchemaName = SchemaName,
               @TableName = TableName,
               @ForeignKeyName = ForeignKeyName 
        FROM @UntrustedForeignKeys
        WHERE UntrustedForeignKeyID = @Counter;

        PRINT N'Attempting to trust ' + @SchemaName + N'.' + @TableName + N'.' + @ForeignKeyName;

        SET @SQL = N'USE ' + QUOTENAME(@DatabaseName) + N'; '
                 + N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) 
                 + N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(@ForeignKeyName) + N';';
        EXEC(@SQL);

        SET @Counter = @Counter + 1;
    END;
END;
GO

2025-02-27