Changing Quoted_Identifier on existing Constraints / Rules / Procedures

by DLA2014   Last Updated January 13, 2018 21:06 PM

I am currently working with an old-ish database on a 2008-r2 Server which uses a lot of objects that were created with Quoted Identifier set to off.

I am mainly looking at these types:

CHECK_CONSTRAINT
DEFAULT_CONSTRAINT
RULE
SQL_SCALAR_FUNCTION
SQL_STORED_PROCEDURE
SQL_TRIGGER
VIEW

I am now trying to change the Quoted Identifier Setting which had me stumped right away as I discovered that I can't even alter a Constraint.

For the constraints: I am thinking that I have to somehow make a temporary clone/copy, delete the original and then recreate them using the copy and the Quoted_Identifier set to ON, but I don't really know how to do this or how to automate this as my SQL-skills are limited. Could someone help me? Or does somebody know of an easier alternative method?


Edit: Fixed a mistake, added missing information plus this:

I have discovered the INFORMATION_SCHEMA.CHECK_CONSTRAINTS Table which contains the Check Clause which I can use to recreate the constraints. But while I was trying to do so I discovered that I can't set the Quoted_Identifier Flag in this way.

To check if I was successful I use this Statement:

SELECT SCHEMA_NAME(schema_id) AS Schema_, name AS Name_, type_desc AS Typ, type, object_id,
    OBJECTPROPERTY(object_id, 'IsQuotedIdentOn') AS IsQuotedIdentOn
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, 'IsQuotedIdentOn') = 0 
ORDER BY Schema_,type_desc, name

This gives me all the Objects and by using their object_id, their Quoted_Identifier "Flag".

Is my method for checking the setting flawed? Am I simply not setting it the right way? OR Is what I am doing completely stupid as it is no possible to set it for constraints?



Related Questions


ANSI_NULLS and QUOTED_IDENTIFIERS mismatch

Updated January 13, 2018 22:06 PM

Quoting columns with spaces in PostgreSQL?

Updated October 26, 2017 22:06 PM

Blitz procedure failing

Updated January 13, 2018 21:06 PM

Strange behaviours with SET options in SSMS

Updated January 13, 2018 21:06 PM

SQL Server Agent: QUOTED_IDENTIFIER

Updated January 13, 2018 21:06 PM