Preparing for SQL 2008 and Beyond

Been doing some SQL maintenance to take care of some deprecated features in SQL. We want to change all text/ntext types to VARCHAR(MAX) or NVARCHAR(MAX). We also made heavy use of Default Object Bindings in the past and want to change these to Default Constraints. While I'm at it I'll also check for any columns without defaults.

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.