Cómo listar todas las claves foráneas con" WITH NOCHECK " en SQL Server

SELECT * FROM sys.foreign_keys AS f Where Is_Not_Trusted = 1

El siguiente script generará las instrucciones alter que comprobarán los datos existentes y evitarán cualquier nueva violación de claves foráneas que no sean de confianza ('with nocheck').

Ejecútelo en SQL Server Management Studio para generar los scripts y luego cópielos en una ventana de consulta para ejecutarlos.

select
    'alter table ' + quotename(s.name) + '.' + quotename(t.name) + ' with check check constraint ' + 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 
    fk.is_not_trusted = 1

CON NOCHECK solo se deben aplicar temporalmente a FK, o se vuelven inútiles para el optimizador como señala su artículo vinculado. De BOL:

El optimizador de consultas no considera restricciones que se definen CON NOCHECK. No se tienen en cuenta esas limitaciones hasta que se vuelvan a habilitar usando ALTERAR TABLA RESTRICCIÓN DE COMPROBACIÓN DE TABLA TODO.

Esto identificará todas sus claves Foráneas: (trabajando en el CON NOCHECK trozo...)

SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER], 
       C.TABLE_SCHEMA [PKTABLE_OWNER], 
       C.TABLE_NAME [PKTABLE_NAME], 
       KCU.COLUMN_NAME [PKCOLUMN_NAME], 
       C2.TABLE_CATALOG [FKTABLE_QUALIFIER], 
       C2.TABLE_SCHEMA [FKTABLE_OWNER], 
       C2.TABLE_NAME [FKTABLE_NAME], 
       KCU2.COLUMN_NAME [FKCOLUMN_NAME], 
       RC.UPDATE_RULE, 
       RC.DELETE_RULE, 
       C.CONSTRAINT_NAME [FK_NAME], 
       C2.CONSTRAINT_NAME [PK_NAME], 
       CAST(7 AS SMALLINT) [DEFERRABILITY] 
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS C 
       INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU 
         ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA 
            AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME 
       INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 
         ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
            AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 
       INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2 
         ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA 
            AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME 
       INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
         ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA 
            AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME 
            AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION 
WHERE  C.CONSTRAINT_TYPE = 'FOREIGN KEY'

Ref .

Como un aparte, tanto en SQL Server 2000 como en 2005, puede verificar si algún dato viola una restricción usando:

DBCC CHECKCONSTRAINTS (table_name)

El siguiente código recupera todas las claves foráneas que están marcadas con 'WITH NOCHECK' y luego usa una instrucción ALTER para corregirlas:

-- configure cursor on all FKs with "WITH NOCHECK"
DECLARE UntrustedForeignKeysCursor CURSOR STATIC FOR
    SELECT  f.name,
            t.name 
    FROM    sys.foreign_keys AS f
            LEFT JOIN sys.tables AS t 
                ON f.parent_object_id = t.object_id 
    Where   Is_Not_Trusted = 1
OPEN UntrustedForeignKeysCursor

-- loop through the untrusted FKs
DECLARE @FKName varchar(100)
DECLARE @TableName varchar(100)
FETCH NEXT FROM UntrustedForeignKeysCursor INTO @FKName, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

    -- Rebuild the FK constraint WITH CHECK
    EXEC ('ALTER TABLE ' + @TableName + ' WITH CHECK CHECK CONSTRAINT ' + @FKName)

    -- get next user
    FETCH NEXT FROM UntrustedForeignKeysCursor INTO @FKName, @TableName

END

-- cleanup
CLOSE UntrustedForeignKeysCursor

Sé que esta es una vieja pregunta con algunas viejas respuestas que tienen buena información. Sin embargo, solo quería compartir un script que he estado utilizando para abordar esta área del problema en bastantes bases de datos diferentes para nosotros:

-- Foreign Keys
SELECT 'ALTER TABLE ' + o.name + ' WITH CHECK CHECK CONSTRAINT ' + i.name + ';' AS AlterStatement
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
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0;
GO

-- Constraints
SELECT 'ALTER TABLE ' + o.name + ' WITH CHECK CHECK CONSTRAINT ' + i.name + ';' AS AlterStatement
from sys.check_constraints 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
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0;
GO

Esto generará una colección de instrucciones ALTER para solucionar este problema "NOCHECK" con claves foráneas y restricciones. Esto se basa en algunas consultas proporcionadas por Brent Ozar pero modificadas por mí para mis propósitos y facilidad de uso. Esto podría ser fácilmente ajustado con un UNION para que sea una sola consulta.

Para su información, he utilizado esto exclusivamente en entornos de base de datos SQL de Azure. No estoy seguro de si hay limitaciones en las versiones anteriores de SQL Server, pero funciona muy bien en Azure.