Updating Default Values for Columns

Updating Default Values for Columns

Updating Default Values for Columns 150 150 Carlos L Chacon

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.


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.

[sourcecode language=”SQL”]
‘ALTER TABLE ‘ + t.name + ‘ DROP CONSTRAINT ‘ + dc.name + ‘
‘ + ‘ALTER TABLE ‘ + t.name + ‘ ADD CONSTRAINT ‘ + dc.name + ‘ DEFAULT GETUTCDATE() FOR ‘ + c.name + ‘
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())’

Imagine what’s possible with a dedicated SQL specialist on your team.

Leave a Reply

Back to top