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
add comment

1 Answer 1

up vote 5 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
3  
    
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
add comment

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.