Programmatically set column nullable

Question

I would like to remove not null constraint on a column, whatever its type is (nvarchar, bingint, smallint).

I am pretty sure it can be achieved using sp_executesql and building the ALTER TABLE xxx ALTER COLUMN cccc type NULL statement (by getting column type information from INFORMATION_SCHEMA.COLUMNS).

Is there any other way?

Answer 1

create procedure sp_RemoveNotNullConstraint
(
    @tableName nvarchar(255),
    @columnName nvarchar(255)
)
as
begin

    declare @dataType nvarchar(255)
    declare @sql nvarchar(max);

    select @dataType =
        case 
            when C.CHARACTER_MAXIMUM_LENGTH is not null 
                then C.DATA_TYPE + '(' + CAST(C.CHARACTER_MAXIMUM_LENGTH as nvarchar(255)) + ')'
            else C.DATA_TYPE
        end
    from INFORMATION_SCHEMA.COLUMNS C
    where C.TABLE_NAME = @tableName AND C.COLUMN_NAME = @columnName

    set @sql = 'ALTER TABLE ' + @tableName + ' ALTER COLUMN ' + @columnName + ' ' + @dataType + ' NULL;';
    exec sp_executesql @sql;

end
go