Today five people showed up in my cube at 5:15 saying that the new development databases are missing defaults. I did some checking and the default objects were there, the columns with default constraints were there, but the columns with bindings to the default objects were missing the bindings. I created a script to run on the source database. The output of this script will be a script to run on the destination database to get things in sync.
Hopefully you won’t need it, with new tools like VS2010 and Redgate’s SQL Compare, but here it is if you do:
Select
'EXECUTE sp_bindefault ''dbo.' + SO.Name + ''', N''dbo.' + OBJECT_NAME(SC.object_id) + '.' + SC.name + ''''
from sys.columns SC
Join sys.objects SO on SO.object_id = SC.default_object_id and type = 'D' and parent_object_id = 0
Order by SO.Name
