>>>>>>
>>>>>> 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
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.