SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Change the Settings of a Database Object Using Powershell

By Ravi Kumar SV,

In this article, I will show how to change the QUOTED_IDENTIFIER and ANSI_NULLS settings of a database object using Powershell. We may be required to change these settings without changing the contents/definition of a database object. For example, when you add a computed column in a table, it  will impact the stored procedures referring to this table. If you have the above setting OFF, this requires all the objects that were created with these settings ON to be recompiled.

Suppose you receive a requirement to add a persisted computed column in one table and once you add this column in the table, the stored procedure or the trigger that performs an INSERT on this table fails with the following error:

Msg 1934, Level 16, State 1, Procedure usp_Proc1, Line 29
INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

The above error says that the ANSI_NULLS and QUOTED_IDENTIFIER settings are OFF, but should be set to ON to get rid of this error. There are many ways we can change these settings from OFF to ON, but I chose Powershell. Now I will show how to write a Powershell script to change these settings to ON.

Below are the simple four steps to write the Powershell script:

  1. Add a reference to the .Net SMO Assembly.

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
  2. Create a new SMO server object, which makes connection to your SQL Server instance. Replace “SERVER1” with the relevant server name.
    $svr = new-object ("Microsoft.SqlServer.Management.Smo.Server") "SERVER1"
  3. Create a new database variable referring to the database used to change the settings of the database objects. Replace “Database1” with the relevant database name.

    $db = $svr.Databases["Database1"]
  4. Start a foreach loop to connect to all the stored procedures in the database. For each object check if the object has QUOTED_IDENTIFIER and ANSI_NULLS are set to OFF (False). If so, change the setting to ON (True). After changing the setting to ON, change the TextMode propery to False to specify that the text header is not editable and finally alter the objects using Alter() method of the stored procedure object.
    foreach ($sp in $db.StoredProcedures) {
        if ($sp.IsSystemObject -eq $False) {
            if ($sp.QuotedIdentifierStatus -eq $False) {
                $sp.QuotedIdentifierStatus = $True;
            if ($sp.AnsiNullsStatus -eq $False) {
                $sp.AnsiNullsStatus = $True;
            $sp.TextMode = $False
  5. Step 4 can be repeated for triggers or other database objects by referring to the objects inside the same database object variable ($db).

I can write the same script in VB or C#, but I found Powershell to be a simple tool for writing this type of script. This is easier for the Administrator who deploys the scripts in the live server.

You may be interested in these reference links on Powershell:

Total article views: 2138 | Views in the last 30 days: 2
Related Articles

Change Database Collation

A stored procedure to automate database collation change


dynamic object(database/schema/obj) in stored procedure

how to reference multiple database/objects in stored procedure (parameterization)


Stairway to SQL PowerShell Level 4: Objects in SQL PowerShell

This far, we have learned about installation and setup of the PowerShell environment. You should now...


Which Stored Procedure changed

Which Stored Procedure changed


PowerShell for Server/DB/Object level Permissions/Logins/Roles/Users

PowerShell to generate Server Level Permissions/Logins/Roles, Object/Database Level Permissions.