Как найти подпрограммы, созданные с выключенным параметром SET ANSI_NULLS?
Я создал отфильтрованный некластеризованный индекс, чтобы оптимизировать определенный набор запросов, но начал получать следующие ошибки из разных источников:
UPDATE failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Кажется, что некоторые из устаревших подпрограмм созданы с опцией SET ANSI_NULLS OFF, и когда движок пытается обновить целевую таблицу из заданного контекста, возникает ошибка.
Мне интересно, есть ли способ увидеть, какие из подпрограмм созданы с этой опцией. Например, если вы напишете такую процедуру, вы получите что-то вроде этого:
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE ....
Я обычно использую скрипты, подобные приведенному ниже, чтобы найти вещи в определениях объектов, но эти настройки не являются его частью:
DECLARE @SearchWord NVARCHAR(128) = 'SET ANSI_NULLS OFF'
SELECT [ROUTINE_NAME]
FROM [INFORMATION_SCHEMA].[ROUTINES]
WHERE [ROUTINE_DEFINITION] LIKE '%' + @SearchWord+'%'
UNION
SELECT OBJECT_NAME([id])
FROM [SYSCOMMENTS]
WHERE [text] LIKE '%' + @SearchWord + '%'
GROUP BY OBJECT_NAME([id])
UNION
SELECT OBJECT_NAME(object_id)
FROM [sys].[sql_modules]
WHERE [definition] LIKE '%' + @SearchWord + '%' ;
Я надеялся, что это хранится в каком-то системном представлении: D, и я смогу просто запросить его: D
Он точно где-то хранится. Я не знаю где, но SSMS знает.
sys.sql_modules твой друг.
Ответы 2
Вы можете просто установить SQL Search от Redgate или аналогичные продукты от других поставщиков. Тогда легко искать такие вещи.
Для одноразового, что-то вроде
SELECT * FROM sys.sql_modules WHERE definition LIKE '% ansi %'
должен сделать это. или
SELECT * FROM sys.sql_modules WHERE definition uses_ansi_nulls = 1
простой поиск может сделать это, например, вот так
SELECT DISTINCT
o.name AS Object_Name,
o.type_desc,
m.*
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.uses_ansi_nulls = 0
Я бы записал всю базу данных (все объекты, каждый объект в отдельный файл) с помощью SSMS, а затем grep для
SET ANSI_NULLS OFF