How to add a default value to an already existing column?

sql sql-server tsql alter-table default-constraint
Earlz picture Earlz · Jun 17, 2010 · Viewed 50.6k times · Source

I have an existing column in my SQL Server database. I have tried about everything I can think of but can not get a default value to be added to the column. What works in every other database is 

alter table mytable 
  alter column mycolumn set default(now()) --mycolumn is a datetime

How do I do this in SQL Server?

The error I get for that exact syntax is incorrect syntax near the keyword 'set'

Answer

OMG Ponies picture OMG Ponies · Jun 17, 2010

Use:

ALTER TABLE dbo.mytable
ADD CONSTRAINT def_mycolumn DEFAULT GETDATE() FOR mycolumn

For more info, see: Working with Default Constraints

Related questions

Altering column size in SQL Server

How to change the column size of the salary column in the employee table from numeric(18,0) to numeric(22,5)

Read More
Add column to table and then update it inside transaction

I am creating a script that will be run in a MS SQL server. This script will run multiple statements and needs to be transactional, if one of the statement fails the overall execution is stopped and any changes are …

Read More
Add a column if it doesn't exist to all tables?

I'm using SQL Server 2005/2008. I need to add a column to a table if it does not yet exist. This will apply to all tables in a given database. I hoped I was close, but I'm having issues with this …

Read More