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