2

is there a way to identify whether the object is created with SET QUOTED_IDENTIFIER on or off?? (not only this set option, am lookin for a script to identify all the ANSI settings associated to the object)

this is a follow up to the questions in staack link - 1 and Link-2

i ve been getting the SET QUOTED_IDENTIFIER error on a single table (which got indexed view). I have verified all the settings, scripts are fine. and it created perfectly with correct settings.

It gives the error all of a sudden after applying some db fixes (not our script)..

Note: sorry to open a new thread, i dint get any possible solution to my first link - 1 thread, thought its dead

Community
  • 1
  • 1
RameshVel
  • 64,778
  • 30
  • 169
  • 213

2 Answers2

3

For code and check constraints:

SELECT 
    OBJECTPROPERTYEX (OBJECT_ID('MyProc'), 'ExecIsQuotedIdentOn')
    OBJECTPROPERTYEX (OBJECT_ID('MyCK'), 'IsQuotedIdentOn')

I can't find anything for tables/indexes in system views or functions.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the table's metadata even if the option is set to OFF when the table is created. – Tewr Dec 17 '19 at 14:41
0
select
    'Object created with dangerous SET Option' [Finding]
    ,o.[type_desc] [Type]
    ,QUOTENAME( SCHEMA_NAME( o.[schema_id] ) ) [Schema]
    ,QUOTENAME( OBJECT_NAME( sm.[object_id] ) ) [Name]
    ,sm.[uses_ansi_nulls] [ANSI NULL]
    ,sm.[uses_quoted_identifier] [QUOTED]
    ,sm.[definition]
from [sys].[sql_modules] sm
join [sys].[objects] o on o.[object_id] = sm.[object_id]
    and (
        sm.[uses_ansi_nulls] != 1
        or sm.[uses_quoted_identifier] != 1
        )
    and o.[is_ms_shipped] = 0;
Protiguous
  • 89
  • 2
  • 9