Changing Bound Defaults to Default Constraints

Published 11 June 09 06:50 PM | MartinBell
I answered a question on the newsgroups today about how to do this. Books Online says that CREATE DEFAULT, sp_bindefault and sp_unbindefault will be removed in future versions. I can’t think of any reasons why you can’t plan to remove them now as part of your release cycle and eliminate the additional work from a future upgrade!!

Looking at a way to distinguish DEFAULT constraints from bound defaults, I noticed that there was not entry in sys.default_constraints. But then I realised that it would be easier to check the ‘IsDefault’ property in the OBJECTPROPERTY function. Unfortunately I couldn't find an easy way to obtain the constant_expression from the CREATE DEFAULT statement, as the whole statement is held in the text of sys.comments and I couldn’t find it anywhere else! (if you know a different way let me know!!) I therefore made the assumption that the first occurrence of 'AS' in the definition would be before the constant_expression, the constant_expression can be something like
CAST(1 as int) which means that you can’t rely on it being the last occurence!!!

I came up with the script:

SELECT
'EXEC sp_unbindefault '''
+ QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + '.' + QUOTENAME(c.name)
+ '''; ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
+ ' ADD CONSTRAINT [DF_' +  o.name +  '_' + c.name
+ '] DEFAULT ' + REPLACE(RIGHT(CAST(t.text as varchar(max)), 
             
LEN(CAST(t.text as varchar(max))) - CHARINDEX('AS', CAST(t.text as varchar(max)))-1 ),';','')
+ ' FOR ' + QUOTENAME (c.name ) + ' ;'
FROM sys.columns c
JOIN sys.syscomments t ON c.default_object_id = t.id
JOIN sys.objects o ON o.object_id = c.object_id
JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE OBJECTPROPERTY(c.default_object_id, 'IsDefault') = 1

Once you have removed all bindings to the defaults the DEFAULTs can then be removed.

SELECT 'DROP DEFAULT ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ';'
FROM sys.objects o
JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE OBJECTPROPERTY(o.object_id, 'IsDefault') = 1

As I have made some assuptions here you should run test it works on a backup of your own system before implementing it on live.

Comments

# Dew Drop – June 12, 2009 | Alvin Ashcraft's Morning Dew said on June 12, 2009 01:40 PM:

Pingback from  Dew Drop – June 12, 2009 | Alvin Ashcraft's Morning Dew

# Log Buffer #150 | Pythian Group Blog said on June 12, 2009 05:12 PM:

Pingback from  Log Buffer #150 | Pythian Group Blog

# Log Buffer #150 | WORDPRESS-TEMPLATES-PLUGINS-RSS | Log Buffer #150 >>> said on June 12, 2009 06:00 PM:

Pingback from  Log Buffer #150 | WORDPRESS-TEMPLATES-PLUGINS-RSS |   Log Buffer #150 >>>

# Log Buffer #150 | WORDPRESS-TEMPLATES-PLUGINS-RSS | Log Buffer #150 >>> said on June 16, 2009 08:01 PM:

Pingback from  Log Buffer #150 | WORDPRESS-TEMPLATES-PLUGINS-RSS |   Log Buffer #150 >>>

This Blog

SQL Blogs

Syndication