Кто-нибудь знает запрос для перечисления всех внешних ключей в базе данных с описанием "WITH NOCHECK", применяемым к нему? (удаление их повысит производительность и стабильность).
Как перечислить все внешние ключи с помощью "WITH NOCHECK" в SQL Server
Ответ 1
Далее будут возвращены имена внешних ключей в текущей базе данных, которые отключены, то есть WITH NOCHECK
Для SQL Server 2005/2008:
select * from sys.foreign_keys where is_disabled=1
В ответе было некоторое обсуждение различий между инвалидами и не доверенными. Что ниже объясняет разницу Здесь некоторый код, чтобы прояснить разницу между is_disabled и isnotrusted.
-- 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
означает, что ограничение отключено
isnottrusted
означает, что SQL Server не верит, что столбец был проверен для таблицы внешнего ключа.
Таким образом, нельзя предположить, что повторное включение ограничения внешнего ключа будет оптимизировано. Чтобы оптимизатор доверял столбцу, лучше всего отказаться от ограничения внешнего ключа и заново создать его с помощью параметра WITH CHECK
(4.)
Ответ 2
SELECT * FROM sys.foreign_keys AS f Where Is_Not_Trusted = 1
Ответ 3
Следующий script будет генерировать инструкции alter, которые будут проверять существующие данные и предотвращать любые новые нарушения для внешних ключей, которые в настоящее время не доверяют ( "с nocheck" ).
Выполните его в SQL Server Management Studio для создания сценариев, а затем скопируйте их в окно запроса, чтобы выполнить их.
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
Ответ 4
С NOCHECK следует всегда применять FK временно, или они становятся бесполезными для оптимизатора, как указывает ваша связанная статья. От BOL:
Оптимизатор запросов не учитывает ограничения, определенные с помощью NOCHECK. Такие ограничения игнорируются пока они не будут повторно включены, используя ПРОВЕРИТЬ КОНСТРУКЦИЮ ALTER TABLE ALL.
Это будет идентифицировать все ваши внешние ключи: (работает с битом WITH NOCHECK...)
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'
В стороне, как в SQL Server 2000, так и в 2005 году, вы можете проверить, не нарушает ли какие-либо данные ограничение, используя:
DBCC CHECKCONSTRAINTS (table_name)
Ответ 5
Следующий код извлекает все внешние ключи, отмеченные "WITH NOCHECK", а затем использует оператор ALTER для их исправления:
-- 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
Ответ 6
Я знаю, что это старый вопрос с некоторыми старыми ответами, в которых есть хорошая информация. Однако я просто хотел поделиться script, который я использовал для решения этой проблемной области, в нескольких разных базах данных для нас:
-- 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
Это создаст набор операторов ALTER, чтобы исправить эту проблему "NOCHECK" с помощью внешних ключей и ограничений. Это основано на некоторых запросах, предоставленных Brent Ozar, но измененных мной для моих целей и простоты использования. Это можно легко настроить с помощью UNION
, чтобы сделать его одним запросом.
FYI, я использовал это исключительно в среде Azure SQL Database. Я не уверен, существуют ли ограничения для старых версий SQL Server, но он отлично работает в Azure.