How to allow null in all columns of a table in sql server 2008?

Question

I have an empty table where all columns were set to "NOT NULL". I want to set it to ALLOW NULL without recreating the table.

How do I do this ? I am using SQL server 2008 r2 and management studio.

EDIT- I want to set all columns to allow null without having to know the datatype of the column. That is, set all to null while keeping the data types the same as before.

@MitchWheat - I don't understand what you said. Please explain it in more detail. — sequel.learner, Mar 15 '13 at 10:29
Why do you want to do this? If all columns are `NULL`-able the table won't be able to have a primary key. — Martin Smith, Mar 15 '13 at 10:29
@TimSchmelter - Not exactly a duplicate. I want to set all to allow null without having to know the datatype of the column. — sequel.learner, Mar 15 '13 at 10:30
The syntax for `ALTER COLUMN` requires you to know the datatype. You can look it up in the catalogue views such as `sys.columns` but in the end the SQL you submit must include it. — Martin Smith, Mar 15 '13 at 10:31
@sql.learner: being able to do something, and it being a good idea to do so are not necessarily the same thing. — Mitch Wheat, Mar 15 '13 at 10:32
@MartinSmith - This table is just a dump for data. This data does not need a PK. Its as if the table was a csv or flat file. — sequel.learner, Mar 15 '13 at 10:33

score 0 · Accepted Answer · answered Mar 15 '13 at 12:40

ALTER COLUMN Specifies that the named column is to be changed or altered. The modified column cannot be any one of the following:

A column with a timestamp data type.

• The ROWGUIDCOL for the table.

• A computed column or used in a computed column.

• Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, the new size is equal to or larger than the old size, and the index is not the result of a PRIMARY KEY constraint.

• Used in statistics generated by the CREATE STATISTICS statement unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or greater than the old size, or if the column is changed from not null to null. First, remove the statistics using the DROP STATISTICS statement. Statistics that are automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.

• Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.

• Used in a CHECK or UNIQUE constraint. However, changing the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.

• Associated with a default definition. However, the length, precision, or scale of a column can be changed if the data type is not changed.

The data type of text, ntext and image columns can be changed only in the following ways: ◦ text to varchar(max), nvarchar(max), or xml

◦ ntext to varchar(max), nvarchar(max), or xml

◦ image to varbinary(max)

Some data type changes may cause a change in the data. For example, changing an nchar or nvarchar column to char or varchar may cause the conversion of extended characters. For more information, see CAST and CONVERT (Transact-SQL). Reducing the precision or scale of a column may cause data truncation.

The data type of a column of a partitioned table cannot be changed.

The document can be found here: http://msdn.microsoft.com/en-us/library/ms190273(v=sql.105).aspx

bad answer and not useful. But, i'll take the points. Thank you. — sequel.learner, Mar 16 '13 at 01:17

How to allow null in all columns of a table in sql server 2008?

1 Answers1