Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 7 down vote accepted

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.

share|improve this answer
Thank you.. But then what about the data in your table? –  mahesh 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
thank you very much... working perfectly.. –  mahesh Apr 9 '12 at 10:30
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 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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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