Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 6 down vote accepted

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

share|improve this answer
    
Excellent, thanks a lot, Aaron! Too bad it only shows two (ansi_nulls and quoted_identifier) - but better than nothing! –  marc_s Aug 14 '12 at 14:15
4  
    
Thanks for that info, @Andomar! –  marc_s Aug 14 '12 at 14:48
2  
Huh, didn't know you could drop multiple procedures like that in one statement. Nice! –  Jon Seigel Aug 14 '12 at 20:16
    
@Jon works for several DML commands, I usually use it for DROP TABLE not DROP PROCEDURE. –  Aaron Bertrand Aug 14 '12 at 20:24

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
;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.