Thought I'd share some useful SQL scripts in case you are embarking on the same adventure.
Find all text/ntext columns in a database:
SELECT
e.name as table_name, c.name as column_name, t.name as type_name
FROM
sys.columns c
JOIN
sys.tables e
ON
e.object_id = c.object_id
LEFT JOIN
sys.all_objects ex
ON
ex.object_id = c.default_object_id
JOIN
sys.types t
ON c.user_type_id = t.user_type_id
Where OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0
and t.name in ('text','ntext')
order by 1, 2
This is useful for finding any column type in your database just by changing the criteria. Using this I quickly was able to create scripts to update our development, test and production databases.
Find columns with NO defaults defined:
SELECT
e.name as table_name, c.name as column_name
FROM
sys.columns c
JOIN
sys.tables e
ON
e.object_id = c.object_id
LEFT JOIN
sys.all_objects ex
ON
ex.object_id = c.default_object_id
Where OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0
and ex.name is null
order by 1, 2
Now find all the bound Defaults:
SELECT
e.name as table_name, c.name as column_name,
ex.name as default_object
FROM
sys.columns c
JOIN
sys.tables e
ON
e.object_id = c.object_id
LEFT JOIN
sys.all_objects ex
ON
ex.object_id = c.default_object_id
Where OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0
and ex.name in ('UW_NullDefault','UW_SpaceDefault','UW_ZeroDefault')
order by 1, 2
And this script will automatically replace the bound default with a default constraint:
BEGIN
DECLARE @FixList TABLE
(
Number INT NOT NULL PRIMARY KEY CLUSTERED,
TableName SYSNAME NOT NULL,
ColumnName SYSNAME NOT NULL,
DefaultObject SYSNAME NOT NULL
)
INSERT @FixList
(
Number,
TableName,
ColumnName,
DefaultObject
)
SELECT
Number = ROW_NUMBER() OVER (ORDER BY e.name, c.name),
e.name as table_name, c.name as column_name,
ex.name as default_object
FROM
sys.columns c
JOIN
sys.tables e
ON
e.object_id = c.object_id
LEFT JOIN
sys.all_objects ex
ON
ex.object_id = c.default_object_id
JOIN
sys.types t
ON c.user_type_id = t.user_type_id
Where OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0
and ex.name in ('UW_NullDefault','UW_SpaceDefault','UW_ZeroDefault')
order by 1, 2
DECLARE @RowID INT, @SQL VARCHAR(1000)
SELECT @RowID = 1, @SQL = ''''
DECLARE @TableName SYSNAME
DECLARE @ColumnName SYSNAME
DECLARE @DefaultObject SYSNAME
DECLARE @I INT
SET @I = 1
SELECT @RowID = Number, @TableName = TableName, @ColumnName = ColumnName, @DefaultObject = DefaultObject FROM @FixList WHERE NUMBER = @I
WHILE @TableName IS NOT NULL
BEGIN
-- unbind default object
BEGIN TRY
SET @SQL = 'EXECUTE sp_unbindefault N''['+@TableName+'].['+@ColumnName+']'''
PRINT @SQL
EXEC(@SQL)
BEGIN
IF @DefaultObject = 'UW_SpaceDefault'
SET @SQL = 'ALTER TABLE ['+@TableName+'] ADD CONSTRAINT DF_'+@TableName+'_'+@ColumnName+' DEFAULT ('''') FOR ['+@ColumnName+'] '
ELSE IF @DefaultObject = 'UW_ZeroDefault'
SET @SQL = 'ALTER TABLE ['+@TableName+'] ADD CONSTRAINT DF_'+@TableName+'_'+@ColumnName+' DEFAULT (0) FOR ['+@ColumnName+'] '
ELSE
SET @SQL = 'ALTER TABLE ['+@TableName+'] ADD CONSTRAINT DF_'+@TableName+'_'+@ColumnName+' DEFAULT (NULL) FOR ['+@ColumnName+'] '
END
PRINT @SQL
EXEC(@SQL)
SET @I = @I + 1
SELECT @RowID = Number, @TableName = TableName, @ColumnName = ColumnName, @DefaultObject = DefaultObject FROM @FixList WHERE Number = @I
IF @@ROWCOUNT = 0 BREAK
END TRY
BEGIN CATCH
PRINT 'Failed on table: '+@TableName+' Column: '+@ColumnName
BREAK
END CATCH
END
END
Update 1/31/2010:
Just found this post which I borrowed some ideas from to get this working.