Thursday, February 2, 2012

Find all SQL Objects with QUOTED_IDENTIFIER set to OFF

Whenever you create or recreate an SP, make sure QUOTED_IDENTIFIER is set to ON. The default value of OFF is there for backward compatibility only. Many modern features of SQL Server (such as filtered indexes, computed persisted columns, xml columns ) require Quoted Identifiers enabled.

Here’s is the script to list all objects with QUOTED_IDENTIFIER set to OFF

  1: SELECT  SCHEMA_NAME(s.[schema_id]) + '.' + AS name
  2:        ,s.create_date
  3:        ,s.modify_date
  4:        ,OBJECTPROPERTY(s.[object_id], 'ExecIsQuotedIdentOn') AS IsQuotedIdentOn
  5:        ,s.type_desc AS object_type
  6: FROM    sys.objects s
  7: WHERE   s.type IN ( 'P', 'TR', 'V', 'IF', 'FN', 'TF' )
  8:         AND OBJECTPROPERTY(s.[object_id], 'ExecIsQuotedIdentOn') = 0
  9: ORDER BY SCHEMA_NAME(s.[schema_id]) + '.' + DESC 


Post a Comment