Find out what setting were used for a given stored procedure

Question

As a follow-up to my previous question:

is there a way to need to find out what the ANSI_NULLS and ANSI_PADDING etc. settings where when a given procedure was created in my database ?

It seems my customer there has several stored procedures and a few triggers that have been "tweaked" - but unfortunately, using a "wrong" set of those database settings. Can I find out which procs and triggers are affected, or do I need to re-create all of them with the proper settings?

Answer 1

This can be determined from sys.sql_modules:

USE tempdb;
GO
SET ANSI_NULLS ON;
GO
CREATE PROCEDURE dbo.foo1 AS SELECT 1;
GO
SET ANSI_NULLS OFF;
GO
CREATE PROCEDURE dbo.foo2 AS SELECT 1;
GO

SELECT name = OBJECT_NAME([object_id]), uses_ansi_nulls
  FROM sys.sql_modules
  WHERE OBJECT_NAME([object_id]) IN (N'foo1', N'foo2');

GO
DROP PROCEDURE dbo.foo1, dbo.foo2;

Results:

name   uses_ansi_nulls
----   ---------------
foo1   1
foo2   0

Some other useful columns:

object_id
definition
uses_ansi_nulls
uses_quoted_identifier
is_schema_bound
uses_database_collation
is_recompiled
null_on_null_input
execute_as_principal_id

Note that some settings (e.g. arithabort, ansi_warnings, ansi_padding) are not stored with the object. Also you will need to recompile the procedures with the correct settings for any that have been identified as "wrong."