How to remove non-zero constraint in sql server using query

Question

I am trying to remove a non-zero constraint in SQL Server 2008 without data loss.

Answer 1

ALTER TABLE YourTable ALTER COLUMN YourColumn columnType NULL 

Answer 2

Remove restriction not null to null

 ALTER TABLE 'test' CHANGE COLUMN 'testColumn' 'testColumn' datatype NULL; 

Answer 3

Remove column constraint: not null to null

 ALTER TABLE test ALTER COLUMN column_01 DROP NOT NULL; 

Answer 4

For posterity only:

I declared my column name as follows:

 ALTER TABLE <table_name> ADD <column_name> <datatype> NOT NULL DEFAULT 0 

To successfully delete the name <column_name> programmatically, you must declare your column as follows:

 ALTER TABLE <table_name> ADD <column_name> <data_type> NOT NULL CONSTRAINT <default_constraint_name> DEFAULT 0 

To DROP <column_name>:

• ALTER TABLE <table_name> DROP <default_constraint_name>
• ALTER TABLE <table_name> ALTER COLUMN <column_name> <data_type> NULL
• ALTER TABLE <table_name> DROP COLUMN <column_name>

Answer 5

You must remove any restrictions before deleting, not a null column.

 DECLARE @ConstraintName nvarchar(200) SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID('<tableName>') AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = N'<columnToDelete>' AND object_id = OBJECT_ID(N'<columnToDelete>')) IF @ConstraintName IS NOT NULL EXEC('ALTER TABLE <tableNAme> DROP CONSTRAINT ' + @ConstraintName) IF EXISTS (SELECT * FROM syscolumns WHERE id=object_id('<TableName>') AND name='<columnToDelete>') EXEC('ALTER TABLE <tableName> DROP COLUMN <columnToDelete>') 

This works for me.