How to find routines that are created with SET ANSI_NULLS OFF?

Question

I have created a filtered non-clustered index in order to optimize particular set of queries, but I started to get the following errors from various sources:

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.

It seems that some of the legacy routines are created with the SET ANSI_NULLS OFF option and when the engine is trying to update the destination table from given context the error is thrown.

I am wondering is there a way to see which of the routines are created with this option. For example, if you script such routine you get something like this:

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE ....

I usually use scripts like the below one to found things in the objects definitions, but this settings is not part of it:

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 + '%' ;

I'd script out the whole database (all objects, each object into individual file) using SSMS and then grep for the `SET ANSI_NULLS OFF` — Vladimir Baranov, May 27 '19 at 13:25
I was hoping that this is stored in some system view :D and I will be able just to query it :D — gotqn, May 27 '19 at 13:26
It is definitely stored somewhere. I don't know where, but SSMS does know. — Vladimir Baranov, May 27 '19 at 13:27

GuidoG · Answer 1 · 2019-05-27T13:50:38.373

3

a simple search could do this, like this for example

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

edited May 27 '19 at 13:50

answered May 27 '19 at 13:45

GuidoG

11,359
6
44
79

user1443098 · Accepted Answer · 2019-05-27T13:51:49.807

2

You could just install SQL Search from Redgate or similar products from other vendors. Then it's easy to search for things like this.

For a one-off, something like

SELECT * FROM sys.sql_modules WHERE definition LIKE '% ansi %'

should do it. or

SELECT * FROM sys.sql_modules WHERE definition uses_ansi_nulls = 1

see sys.sql_modules (Transact-SQL)

edited May 27 '19 at 13:51

answered May 27 '19 at 13:31

user1443098

6,487
5
38
67

How to find routines that are created with SET ANSI_NULLS OFF?

2 Answers2