Dynamically adding column with user defined datatype

[sodakotahusker]

I need to sweep through all the tables I have manually created and add two fields. The problem is that they both have a user defined data type with a built in default of getdate() and the function for the current user. When I add the new column I'm told the default is already set so I have to just do an add column userDT

alter table tblTargetLUDischargeType
add [InsertDate] dbo.CurrentDate

It won't let me specify not null because there is not default but it won't let me specify a default because there is one assigned to the UDT. This is a catch 22 situation.

Any way around this one???

 

[Lamprey13]

I'm not sure exatly how user defined data types work in this context as I never use them. But, if you are in a catch-22, you might add the column as NULL. Then make sure all the rows have a value, then alter the column to be NOT NULL. I think that will get around your issue.

-Ryan

 

[sodakotahusker]

Thanks Ryan. I tried that already. I got a syntax error near the keyword not

alter table tblLUDischargeType alter column [Insertdate] not null

Wait. I did not include the UDT. I added that and now that seems to take care of it. Thanks!!!