I had to do this recently and needed to look up the syntax as I couldn’t remember it. I don’t do this often, but I was working on a particular item and wanted to test this.

I had a table, something like this:

CREATE TABLE SimpleTable
( MyID INT NOT NULL PRIMARY KEY
, MyChar VARCHAR(200)
, [Status] TINYINT
);

I decide that the [Status] column shouldn’t be NULL and decide to ensure it’s always got a value. I don’t want to rebuild the table, I want to ALTER it. I use this syntax:

ALTER TABLE dbo.SimpleTable
 ALTER COLUMN Status TINYINT NOT NULL;

That’s it. I do this and the column now no longer accepts NULL values.

Of course, if there’s data in there, I need to set those values to something that isn’t NULL before I can do this, but that’s a separate topic.

By the way, I remembered most of this syntax. What I forgot is that I need to include the datatype in there to make this change. There are a number of restrictions with this command, so be sure that you read carefully before you try to make changes. If you do this in SSMS, you might end up with a script for a new table, which may not be what you want to occur in a production environment.

Reference

ALTER TABLE – https://msdn.microsoft.com/en-us/library/ms190273.aspx