>>>>>>>
>>>>>>> DECLARE @aaa varchar(200)
>>>>>>> SELECT @aaa = OBJECT_NAME(constid) FROM sysconstraints WHERE OBJECT_NAME(Id) = 'YourTableName' AND OBJECT_NAME(constid) LIKE '%YourColumnName%'
>>>>>>> IF @aaa IS NOT NULL
>>>>>>> BEGIN
>>>>>>> SET @aaa = 'ALTER TABLE YourTableName DROP CONSTRAINT '+@aaa
>>>>>>> EXEC (@aaa)
>>>>>>> END
>>>>>>>
>>>>>>>
>>>>>>> ALTER TABLE YourTableName ALTER COLUMN ....
>>>>>>>
>>>>>>
>>>>>>Borislav,
>>>>>>
>>>>>>Thank you very much! You code works. However the resulting column loses the default constraint and accepts NULL.
>>>>>>Do I have to create the default constraint, after your code, and then Alter the column and remove the NULL?
>>>>>
>>>>>DROP constraint first if it exists, create not nullable column with the default constraint (you can use the same name for constraint as before)
>>>>
>>>>Thank you.
>>>
>>>You could define your column with the ALTER TABLE command:
>>>
>>>ALTER TABLE ... ALTER COLUMN .... varchar(max) NOT NULL CONSTRAINT DF_TableName_ColumName DEFAULT ''
>>>
>>
>>Can you think of why I am getting the error on the CONSTRAINT? The line is underlined with red and the error:
>>
>>Incorrect syntax near the keyword 'CONSTRAINT'.
>>
>
>Try
>
> ALTER TABLE ... ALTER COLUMN .... varchar(max) NOT NULL
> ALTER TABLE ... ADD CONSTRAINT DF_TableName_ColumnName DEFAULT ('') FOR ColumnName
>
Thank you very much!
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham