UDT change scripting

Brian Donahue

Hi Janko,

SQL Compare is supposed to alter UDDTs exactly as you describe. If it's
not, there must be something wrong. Either your UDDT is not bound to any
columns or SQL Compare didn't pick up the column dependencies properly. Here
is an example script produced by SQL Compare to change a User-Defined Type:

/*
Script created by SQL Compare from Red Gate Software Ltd at 12/07/2004
18:08:20
Run this script on (local).Problems_Beta to make [dbo].[Cmmnt] the same as
on (local).Problems
Please back up your database before running this script
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Unbinding types from columns'
GO
ALTER TABLE Atty ALTER COLUMN [Cmmnt] varchar
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN
TRANSACTION END
GO
PRINT N'Dropping types'
GO
sp_droptype N'Cmmnt'
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN
TRANSACTION END
GO
PRINT N'Creating types'
GO
sp_addtype N'Cmmnt', 'varchar (80)', 'NULL', 'dbo'
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN
TRANSACTION END
GO
PRINT N'Rebinding types to columns'
GO
ALTER TABLE Atty ALTER COLUMN [Cmmnt] [Cmmnt] NULL
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN
TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
@TRANCOUNT>0 BEGIN
PRINT 'The database updated succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO


Regards,

Brian Donahue
Red Gate Technical Support

"Janko Lupsa" <jnai7@hotmail.com> wrote in message
news:QlLoBX$ZEHA.2428@server53...
> SQL Compare makes a lousy script if you want to change a User Defined
> Datatype.
> It just drops and creates another. That is completely useless because
> typically UDT is already bound to a number of tables. I thought that was
to
> hard to do and we created a new database and transferred the data. Now I
> found out a feature in an SQL tool that I use for development (sqlXpress
> IDE). The correct algorythm for handling changes in UDT:
> 1. make a list of all tables/columns that use that UDT
> 2. drop indexes that depend on the columns which use UDT
> 3. use alter column to change all columns that use UDT to its native data
> type
> 4. drop UDT
> 5. create a new UDT with the same name, but different data definition
> 6. use alter column to change native data type for all columns that used
to
> use the old UDT
> 7. recreate all indexes that were dropped in step 2.
>
> sqlXpress doesn't handle step 2, so their script failed, but it gave me an
> idea about altering the column to native data type and back to UDT.
>
> Best regards, Janko Lupsa
>
>