Change the User Defined Data type

IF NOT OBJECT_ID('ChangeType')is NULL
Drop procedure ChangeType
GO
CREATE Procedure ChangeType @TypeName sysname, @NewVal varchar(100)
As

/*
Used to change the user defined data type.


Known Issues:
Procedure will fail if any objects other than tables refer UDF
either directly or indirectly.
Solution: 
Generate Script of Views and Stored Procedures (Create & Drop)
seperately. Run the Drop part first. Then Create & Execute this procedure 
Finally Run the Create part of your views / Stored Procedures etc.

--------------------------------------------------------------------------------------------
-- Written By G.R. Preethiviraj Kulasingham
-- Last Modified on 19th April 2002.
--------------------------------------------------------------------------------------------


*/Declare @SQL Varchar(4000), @Schema sysname, @Table sysname, @Column SysName
Create Table ##Column_Info
(
Table_Schema sysName NOT NULL,
Table_Name sysName NOT NULL,
Column_name sysName NOT NULL
)


insert into ##Column_Info
Select C.Table_Schema, C.Table_Name, C.Column_Name 
from Information_Schema.Columns C, 
Information_Schema.Tables T
Where T.Table_Schema=C.Table_Schema and 
T.Table_Name = C.Table_Name and  
T.Table_Type = 'BASE TABLE' and
C.Domain_Name = @TypeName

Declare Column_Cursor Cursor SCROLL 
FOR SELECT * from ##Column_Info

Open Column_Cursor
Fetch Next From Column_Cursor into @Schema, @Table, @Column
While @@FETCH_STATUS=0
BEGIN
SET @SQL ='ALTER TABLE ['+@Schema+'].['+@Table+'] 
ALTER COLUMN ['+@Column+'] '+@NewVal
Exec(@SQL)
Fetch Next From Column_Cursor into @Schema, @Table, @Column
END  

Exec sp_DropType @TypeName
Exec sp_AddType @TypeName, @NewVal
Fetch FIRST From Column_Cursor into @Schema, @Table, @Column
While @@FETCH_STATUS=0
BEGIN
SET @SQL ='ALTER TABLE ['+@Schema+'].['+@Table+'] 
ALTER COLUMN ['+@Column+'] '+@TypeName
Exec(@SQL)
Fetch Next From Column_Cursor into @Schema, @Table, @Column
END  



close Column_Cursor
Deallocate Column_Cursor
Drop table ##Column_Info
GO