title: How can I alter a smalldatetime column with a default to be datetime?
body: <p>I have a number of columns in my database that were originally created as <code>smalldatetime</code> and that really need to be <code>datetime</code>.  Many of these columns were created with the DDL:</p>
  
  <pre><code>[columnname] smalldatetime not null default getdate()
  </code></pre>
  
  <p>...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.</p>
  
  <p>Of course, I didn't specify a name for the default in the DDL, so the columns all have defaults with names like <code>DF__CaseLock__CaseCo__182C9B23</code>.  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.</p>
  
  <p>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 <em>possible</em> to add a default constraint to an existing column.</p>
  
  <p>It appears that what I have to do is something like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>...only I can only write that script if I know what <code>constraint_name</code> is, because I can't drop a column that's referenced in a constraint.</p>
  
  <p>Is this really that hard?</p>