Quote of the Day

Saturday, January 30, 2010

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.