SELECT	*
FROM
(
	SELECT	name,
		[Status] = CASE WHEN (@@OPTIONS & number) = 0 THEN 'OFF' ELSE 'ON' END
	FROM	master.dbo.spt_values 
	WHERE	    type='SOP'
		AND number > 0
) AS X
WHERE
	(
		   ([name]='ansi_null_dflt_off' AND [Status] <> 'OFF')
		OR ([name]='ansi_null_dflt_on' AND [Status] <> 'ON')
		OR ([name]='ansi_nulls' AND [Status] <> 'ON')
		OR ([name]='ansi_padding' AND [Status] <> 'ON')
		OR ([name]='ansi_warnings' AND [Status] <> 'ON')
		OR ([name]='arithabort' AND [Status] <> 'ON')
		OR ([name]='arithignore' AND [Status] <> 'OFF')
		OR ([name]='concat_null_yields_null' AND [Status] <> 'ON')
		OR ([name]='cursor_close_on_commit' AND [Status] <> 'OFF')
		OR ([name]='disable_def_cnst_check' AND [Status] <> 'OFF')
		OR ([name]='implicit_transactions' AND [Status] <> 'OFF')
		OR ([name]='nocount' AND [Status] <> 'OFF')
		OR ([name]='numeric_roundabort' AND [Status] <> 'OFF')
		OR ([name]='quoted_identifier' AND [Status] <> 'ON')
		OR ([name]='xact_abort' AND [Status] <> 'OFF')
	)
--
ORDER BY [name]

quote:

---

Originally posted by robvolk

Keep in mind that QUOTED_IDENTIFIER is a setting on the procedure declaration, not just a statement within its body. (In fact SET QUOTED_IDENTIFIER should not appear in a procedure body, as its effects can be detrimental)

---

quote:

---

You can determine which procedures do not have that setting with the following:

---

quote:

---

Another best practice is to remove all double quotes from your code, ensure strings are delimited only with single quotes, and (optionally) delimit all object identifiers with square brackets. Yeah it's a lot of work, but doing so renders the QUOTED_IDENTIFIER setting moot.

---

--
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'Test_SET_Values')
	DROP PROCEDURE dbo.Test_SET_Values
GO

CREATE PROCEDURE dbo.Test_SET_Values
AS
/*
 * Test_SET_Values	Test SET values are set to Default Values
 *
 */
DECLARE	@intOPTIONS	int

SELECT	@intOPTIONS = @@OPTIONS	-- Store current value

SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON

DECLARE	@intRetVal	int	-- Return value

SELECT	@intRetVal = 0	-- Assume no error

-- ... all sorts of code removed ...

	SELECT	*
	INTO	#Test_SET_Values_01
	FROM
	(
		SELECT	name,
			[Status] = CASE WHEN (@intOPTIONS & number) = 0 THEN 'OFF' ELSE 'ON' END
		FROM	master.dbo.spt_values 
		WHERE	    type='SOP'
			AND number > 0
	) AS X
	WHERE
		(
			   ([name]='ansi_null_dflt_off' AND [Status] <> 'OFF')
			OR ([name]='ansi_null_dflt_on' AND [Status] <> 'ON')
			OR ([name]='ansi_nulls' AND [Status] <> 'ON')
			OR ([name]='ansi_padding' AND [Status] <> 'ON')
			OR ([name]='ansi_warnings' AND [Status] <> 'ON')
			OR ([name]='arithabort' AND [Status] <> 'ON')
			OR ([name]='arithignore' AND [Status] <> 'OFF')
			OR ([name]='concat_null_yields_null' AND [Status] <> 'ON')
			OR ([name]='cursor_close_on_commit' AND [Status] <> 'OFF')
			OR ([name]='disable_def_cnst_check' AND [Status] <> 'OFF')
			OR ([name]='implicit_transactions' AND [Status] <> 'OFF')
			OR ([name]='nocount' AND [Status] <> 'OFF')
			OR ([name]='numeric_roundabort' AND [Status] <> 'OFF')
			OR ([name]='quoted_identifier' AND [Status] <> 'ON')
			OR ([name]='xact_abort' AND [Status] <> 'OFF')
		)
	--
	ORDER BY [name]

	IF @@ROWCOUNT <> 0
	BEGIN
		SELECT	[Error] = '****** WARNING: Unexpected SET values ******'

		SELECT	[name], [Status]
		FROM	#Test_SET_Values_01
		ORDER BY [name]
	END

	DROP TABLE #Test_SET_Values_01

-- ... more code removed ...

	SET NOCOUNT OFF

	RETURN @intRetVal
GO


-- TEST RIG

-- Set all properties to inappropriate values
SET ansi_null_dflt_off ON
SET ansi_null_dflt_on OFF
SET ansi_nulls OFF
SET ansi_padding OFF
SET ansi_warnings OFF
SET arithabort OFF
SET arithignore ON
SET concat_null_yields_null OFF
SET cursor_close_on_commit ON
-- SET disable_def_cnst_check ON
SET implicit_transactions ON
SET nocount ON
SET numeric_roundabort ON
SET quoted_identifier OFF
SET xact_abort ON
GO

EXEC dbo.Test_SET_Values
GO

-- Reinstate appropriate values
SET ansi_null_dflt_off OFF
SET ansi_null_dflt_on ON
SET ansi_nulls ON
SET ansi_padding ON
SET ansi_warnings ON
SET arithabort ON
SET arithignore OFF
SET concat_null_yields_null ON
SET cursor_close_on_commit OFF
-- SET disable_def_cnst_check OFF
SET implicit_transactions OFF
SET nocount OFF
SET numeric_roundabort OFF
SET quoted_identifier ON
SET xact_abort OFF
GO