How can I alter a smalldatetime column with a default to be datetime?

Question

I have a number of columns in my database that were originally created as smalldatetime and that really need to be datetime. Many of these columns were created with the DDL:

[columnname] smalldatetime not null default getdate()

...which means that in order to alter the column's type, I first have to drop the default, and then re-create the default after altering it.

Of course, I didn't specify a name for the default in the DDL, so the columns all have defaults with names like DF__CaseLock__CaseCo__182C9B23. And when my application creates its database, it does so by executing a script, so the names of the defaults in my customers' databases are (I'm guessing; I haven't verified this) different from their names in mine.

And even if I know the name of the default constraint, I can't figure out the syntax for adding it back in after I drop it. In fact, it's not clear to me that it's possible to add a default constraint to an existing column.

It appears that what I have to do is something like this:

declare @t table (id int not null primary key, date smalldatetime null)
insert into @t (id, date)
    select id, date_column from my_table
drop constraint constraint_name
alter table my_table drop column date_column;
alter table my_table add date_column datetime default getdate()
update my_table set date_column = t.date FROM my_table m JOIN @t t ON m.id = t.ID

...only I can only write that script if I know what constraint_name is, because I can't drop a column that's referenced in a constraint.

Is this really that hard?

Answer 1

You can add a default constraint like this:

ALTER TABLE TableName
ADD CONSTRAINT DF_DefaultName DEFAULT 'Default Value' FOR ColumnName

You can interrogate the sys tables to find the existing constraints. When you add them back in, you should provide an explicit name for them. Here's a blog post that has a number of scripts for dealing with dropping/adding unnamed constraints. Hope that helps!

Answer 2

I'm afraid you have to resort to dynamic SQL.

Bear in mind though that any data that was previously set by the old default will remain.

DECLARE @query varchar(256)

SET @query = (
    SELECT 'ALTER TABLE my_table DROP CONSTRAINT ' + d.name 
    FROM sysobjects d
        INNER JOIN sysobjects t ON t.id = d.parent_obj
    WHERE d.type = 'D'
        AND t.name = 'my_table')

EXEC(@query)

ALTER TABLE my_table
ADD CONSTRAINT DF_date_column default getdate() for date_column

EDIT: Where multiple default columns exist in my_table:

CREATE TABLE #Defaults (
    strName varchar(256) PRIMARY KEY
)

INSERT INTO #Defaults (strName)
SELECT d.name 
FROM sysobjects d
    INNER JOIN sysobjects t ON t.id = d.parent_obj
WHERE d.type = 'D'
    AND t.name = 'my_table'

DECLARE @name varchar(256), @query varchar(256)

SET @name = (SELECT TOP 1 strName FROM #Defaults)

WHILE @name IS NOT NULL
BEGIN        
    SET @query = 'ALTER TABLE my_table DROP CONSTRAINT ' + @name

    EXEC(@query)

    DELETE FROM #Defaults 
    WHERE strName = @name

    SET @name = (SELECT TOP 1 strName FROM #Defaults)
END

ALTER TABLE my_table
ADD CONSTRAINT DF_date_column1 default getdate() for date_column1

ALTER TABLE my_table
ADD CONSTRAINT DF_date_column2 default getdate() for date_column2

DROP TABLE #Defaults