Updating Default Values for Columns

To make life a little simpler, a default value can be added to a column so if the information is not passed with the row data, SQL Server can plug in some information for you.  A common scenario is to add a default DateTime of the current system time.  This image show what that looks like when you design the table in SSMS.

DefaultBinding

What happens if you wanted to change the default value option for those tables?  For example, we are preparing to move to Azure SQL and our default options need to be GETUTCDATE() instead of GETDATE().  I put together this script to help me identify all the constraints in the database that need to be updated. This code has the new lines so you should be able to run the output. Don’t forget to update your data before you made the change. You can find that example here.

SELECT
'ALTER TABLE ' + t.name + ' DROP CONSTRAINT ' + dc.name + '
GO
' + 'ALTER TABLE ' + t.name + ' ADD CONSTRAINT ' + dc.name + ' DEFAULT GETUTCDATE() FOR ' + c.name + '
GO'
FROM sys.default_constraints dc
INNER JOIN sys.tables t ON dc.parent_object_id = t.object_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE dc.parent_column_id = c.column_id
AND dc.definition = '(getdate())'

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s