Auflisten aller Fremdschlüssel mit "WITH NOCHECK" in SQL Server
Kennt jemand eine Abfrage zum Auflisten aller Fremdschlüssel in einer Datenbank, auf die die "WITH NOCHECK" -Beschreibung angewendet wird? (Das Entfernen von ihnen erhöht die Leistung und Stabilität).
Das Folgende gibt den Namen der Fremdschlüssel in der aktuellen Datenbank zurück, die deaktiviert sind, z. B. WITH NOCHECK
Für SQL Server 2005/2008:
select * from sys.foreign_keys where is_disabled=1
In der Antwort wurde etwas über den Unterschied zwischen Behinderten und nicht vertrauenswürdigen Personen diskutiert. Nachfolgend wird der Unterschied erläutert. Hier ist ein Code, der den Unterschied zwischen is_disabled und isnotrusted verdeutlicht.
-- drop table t1
-- drop table t2
create table t1(i int not null, fk int not null)
create table t2(i int not null)
-- create primary key on t2
alter table t2
add constraint pk_1 primary key (i)
-- create foriegn key on t1
alter table t1
add constraint fk_1 foreign key (fk)
references t2 (i)
--insert some records
insert t2 values(100)
insert t2 values(200)
insert t2 values(300)
insert t2 values(400)
insert t2 values(500)
insert t1 values(1,100)
insert t1 values(2,100)
insert t1 values(3,500)
insert t1 values(4,500)
----------------------------
-- 1. enabled and trusted
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO
-- 2. disable the constraint
alter table t1 NOCHECK CONSTRAINT fk_1
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO
-- 3. re-enable constraint, data isnt checked, so not trusted.
-- this means the optimizer will still have to check the column
alter table t1 CHECK CONSTRAINT fk_1
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO
--4. drop the foreign key constraint & re-add
-- it making sure its checked
-- constraint is then enabled and trusted
alter table t1 DROP CONSTRAINT fk_1
alter table t1 WITH CHECK
add constraint fk_1 foreign key (fk)
references t2 (i)
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO
--5. drop the foreign key constraint & add but dont check
-- constraint is then enabled, but not trusted
alter table t1 DROP CONSTRAINT fk_1
alter table t1 WITH NOCHECK
add constraint fk_1 foreign key (fk)
references t2 (i)
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO
is_disabled bedeutet, dass die Einschränkung deaktiviert ist
isnottrusted bedeutet, dass SQL Server nicht darauf vertraut, dass die Spalte mit der Fremdschlüsseltabelle geprüft wurde.
Daher kann nicht davon ausgegangen werden, dass das erneute Aktivieren der Fremdschlüsseleinschränkung optimiert wird. Um sicherzustellen, dass das Optimierungsprogramm der Spalte vertraut, sollten Sie die Fremdschlüsseleinschränkung löschen und mit der WITH CHECK-Option neu erstellen (4.).
SELECT * FROM sys.foreign_keys AS f Where Is_Not_Trusted = 1
Das folgende Skript generiert die alter-Anweisungen, die sowohl vorhandene Daten prüfen als auch neue Verletzungen für Fremdschlüssel verhindern, die derzeit nicht als vertrauenswürdig eingestuft werden ("with nichteck").
Führen Sie es in SQL Server Management Studio aus, um die Skripts zu generieren, und kopieren Sie sie in ein Abfragefenster, um sie auszuführen.
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
WITH NOCHECK sollte immer nur vorübergehend auf FK angewendet werden, oder sie werden für den Optimierer unbrauchbar, wie Ihr verlinkter Artikel darauf hinweist. Von BOL:
Das Abfrageoptimierungsprogramm berücksichtigt Einschränkungen nicht, die WITH NOCHECK definiert sind. Solche Einschränkungen werden Ignoriert, bis sie mit der ALTER TABLE-Tabelle CHECK CONSTRAINT ALL wieder aktiviert werden.
Dadurch werden alle Ihre Fremdschlüssel identifiziert: (Arbeiten am WITH NOCHECK-Bit ...)
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 .
Nebenbei können Sie in SQL Server 2000 und 2005 überprüfen, ob Daten gegen eine Einschränkung verstoßen.
DBCC CHECKCONSTRAINTS (table_name)
Der folgende Code ruft alle Fremdschlüssel ab, die mit 'WITH NOCHECK' gekennzeichnet sind, und verwendet dann eine ALTER-Anweisung, um sie zu reparieren:
-- 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
Ich weiß, das ist eine alte Frage mit einigen alten Antworten, die einige gute Informationen enthalten. Ich wollte jedoch nur ein Skript freigeben, mit dem ich diesen Problembereich in einer Reihe verschiedener Datenbanken angesprochen habe:
-- 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
Dadurch wird eine Sammlung von ALTER-Anweisungen generiert, um dieses "NOCHECK" -Problem mit Fremdschlüsseln und Einschränkungen zu beheben. Dies basiert auf einigen Anfragen von Brent Ozar , wurde aber von mir für meine Zwecke und die Benutzerfreundlichkeit angepasst. Dies könnte leicht mit einer UNION geändert werden, um daraus eine einzelne Abfrage zu machen.
Zu Ihrer Information, ich habe dies ausschließlich in Azure SQL Database-Umgebungen verwendet. Ich bin mir nicht sicher, ob ältere Versionen von SQL Server Einschränkungen haben, aber es funktioniert in Azure hervorragend.