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."

Answer 2

In relation to the ANSI_PADDING portion of your question, you can use sys.columns

From ANSI_PADDING MSDN article:

Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data. ... This setting affects only the definition of new columns. After the column is created, SQL Server stores the values based on the setting when the column was created. Existing columns are not affected by a later change to this setting.

Here is a query to check the ANSI Padding value for object columns of the affected data types:

SELECT TableName = QUOTENAME(SCHEMA_NAME(t.schema_id)) 
                + '.' 
                + QUOTENAME(t.name)
    , ColName = c.name
    , ColTypeName = ty.name
    , c.is_ansi_padded
    , ObjectType = t.[type]
    , ObjectTypeDesc = t.type_desc
FROM sys.columns c 
    INNER JOIN sys.objects t on t.object_id = c.object_id
    INNER JOIN sys.types ty 
        ON ty.system_type_id = c.system_type_id 
        AND ty.user_type_id = c.user_type_id
WHERE ty.name in ('char','varchar','binary','varbinary')
    --AND c.is_ansi_padded = 0 --you can filter on 0 to show the columns that are not following best practices.
ORDER BY 1,2
;