Hi folks. Actually, it is pretty old trick/feature I would like to show you. Let's say we have a table called 'Test1' with one column as VARCHAR(10) which allows inserting NULLs. Well,we want to change this to NOT allow NULLs and I'm also going to change datatype for this column at the same time ,so just for testing let's assume that 'Test1' does not contain alphabetic values.
CREATE TABLE Test1
Col1 varchar(10) NOT NULL,
INSERT INTO Test1 VALUES('1')
EXEC sp_rename 'Test1', 'Test1_Old'
SELECT ISNULL(CAST(Col1 AS int), 0) AS Col1 ---Makes Col1 not to allow NULLs
DROP TABLE Test1_Old
Make sure that you do not have duplicates, so you can create a constraint on the column.
ALTER TABLE Test1
ADD CONSTRAINT PK_Test1
PRIMARY KEY (Col1)