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?