If you’ve scripted out a stored procdure, you’ll have noticed the SET ANSI_NULLS ON or SET QUOTED_IDENTIFIER ON options are also scripted. If your a verteran SQL coder, no doubt you set these before you create any stored procedures. But once a stored procedure is created, how do you determine what options were set?

Well in SQL Server 2005 you will need to use the sys.sql_modules catalogue view. the sys.sql_modules view will show you the options set for definitions of:

  • Defaults
  • Stored Procedures
  • Views
  • DML Trigger
  • Functions (scalar / table)

The code below shows how to view the options of your objects. 

SELECT syso.name, type, sysm.*
FROM sys.sysobjects AS syso
INNER JOIN sys.sql_modules AS sysm
   ON syso.id = sysm.object_id
Tags: SQL Server