T-SQL Tips: Modify All Default Constraints in a Database Based on a Common Value

Context

This blog discusses a method that can be applied for doing the modification of default constraints on multiple fields within a database which currently points to one value

Scenario

Recently in one of my projects there was a need for changing default values linked to all the date fields to make them return GMT date time rather than the local date time. This was for bringing on a standardization between the various in-house systems which were at different geographic locations before bringing the data to the centralized Enterprise Datawarehouse (DWH) for deriving various analytic reports.

Solution 

The solution I proposed was a simple query as below

DECLARE @SQL varchar(max)

 SET @SQL = (select 'ALTER TABLE ' + OBJECT_NAME(parent_object_id) + ' DROP CONSTRAINT ' + name + '; ALTER TABLE ' + OBJECT_NAME(parent_object_id) + ' ADD CONSTRAINT ' + name + ' DEFAULT getutcdate() FOR ' + COL_NAME(parent_object_id,parent_column_id) + ';'
 from sys.default_constraints WHERE definition = '(getdate())' FOR XML PATH(''),TYPE).value('.','varchar(MAX)')

 EXEC( @SQL)

Explanation 

The above solution utilizes the catalog view sys.default_constraints which will contain the details of all the default constraints defined in a database. We are utilizing the below columns in this view for our purpose

parent_object_id - This will hold the object id of the table in which constraint is defined. We will apply OBJECT_NAME function to get back the table name
name - This will give you the name of the default constraint
parent_column_id - This will give the column id of column within the table on which DEFAULT constraint is tied. We apply COL_NAME function to return column name from it
definition - This contains the actual definition of the default constraint. In our case we use this to specifically filter for default utilizing GETDATE function alone to modify them to be based on GETUTCDATE instead.

Now the query explanation
What it does is to generate a SQL string using the information from the above columns to drop and recreate each of the constraints in the database utilizing GETDATE() value and recreate them to point to GETUTCDATE() function to capture GMT date time instead

The generated statement would look like below

ALTER TABLE Table1 DROP CONSTRAINT DefaultConstraint1; ALTER TABLE Table1 ADD CONSTRAINT DefaultConstraint1 DEFAULT getutcdate() FOR column1;
....

Then we make use of EXEC function to dynamically execute the above string to make sure modified constraints are created.
Once this is done you can use the below query to check if changes have been applied to the database correctly.

select count(*) from sys.default_constraints WHERE definition = '(getdate())'

select * from sys.default_constraints WHERE definition = '(getutcdate())'


The first query should return 0 count and second one will return full list of constraints which were previously linked to the getdate function.


Summary

As explained above you can use similar logic to generate the query and execute it so as modify all the default constraints based on a common value in a single pass.

References

sys.default_constraints

http://msdn.microsoft.com/en-us/library/ms173758.aspx