Can we use the following query to update ANSI_NULLS option.

sp_configure 'allow updates', 1

reconfigure with override

update sysobjects set status = status | 0x20000000 where name = 'tblClient'

I am not sure what it is doing. I got this after searching for a solution. Our db is SQL server 2008.

But while i am running this i got an error like this "Ad hoc updates to system catalogs are not allowed."

Please give your suggestions


Updating system tables directly is unsupported and unsafe. The structure of them has changed since 2000 so the code you have won't work against them anyway.

It is sometimes possible to use ALTER TABLE ... SWITCH to do this as a metadata only change however.

A full worked example below.

/*Create table with option off*/ 

CREATE TABLE dbo.YourTable (X INT) 

/*Add some data*/ 
INSERT INTO dbo.YourTable VALUES (1),(2),(3) 

/*Confirm the bit is set to 0*/ 
SELECT uses_ansi_nulls, * 
FROM   sys.tables 
WHERE  object_id = object_id('dbo.YourTable') 


    /*Create new table with identical structure but option on*/
    CREATE TABLE dbo.YourTableNew (X INT) 

    /*Metadata only switch*/
    ALTER TABLE dbo.YourTable  SWITCH TO dbo.YourTableNew;

    DROP TABLE dbo.YourTable; 

    EXECUTE sp_rename N'dbo.YourTableNew', N'YourTable','OBJECT'; 

    /*Confirm the bit is set to 1*/ 
    SELECT uses_ansi_nulls, * 
    FROM   sys.tables 
    WHERE  object_id = object_id('dbo.YourTable') 

    /*Data still there!*/ 
    SELECT * 
    FROM dbo.YourTable


    IF XACT_STATE() <> 0 


One caveat however is that this does not always work. Adding a check constraint with definition CHECK (X <> NULL) causes it to fail with

ALTER TABLE SWITCH statement failed. Table 'YourTableNew' has a column level check constraint 'CK_YourTableNew_X__37A5467C' on column 'X' that is not loadable for semantic validation.

This makes sense as the semantics of the constraint do change with the setting.

If you encounter this you could consider temporarily removing the problematic constraints etc and adding them back after the switch or just biting the bullet and recreating the table in a more conventional way.

| improve this answer | |
  • 1
    Thank you.. But then what about the data in your table? – Mahesh KP Apr 9 '12 at 10:17
  • @mahesh - That's still there. Try running SELECT * FROM dbo.YourTable at the end of the demo code. The data pages are unchanged. Just the table definition is updated. – Martin Smith Apr 9 '12 at 10:18
  • we have got around 100 tables, So we have to do this thing for all the tables. Is there any option to do it more simply like in any one step configuration or any method to automate this thing..:) – Mahesh KP Apr 9 '12 at 10:36
  • @mahesh - You probably could automate it using SMO to script the CREATE table statement or TSQL similar to here. Make sure you have good error handling! – Martin Smith Apr 9 '12 at 10:45
  • I am creating the new table by using the script table as option in SSMS for the particular table. In that case the name of constraints like primary key and foreign key needs to be changed manually. Is any other opton to get rid off this manual editing.. – Mahesh KP Apr 10 '12 at 5:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.