Technical Article

Change the User Defined Data type

,

User Defined Data Types (UDF) is one of the useful concepts in SQL Server. However UDF can't be modified after they are being used. You would have to remove your User Defined Data Type from each table before you can change it. This will be a big job under certain circumstances.

This procedure changes all tables when you change the UDF.
At present I do this only for Tables. This procedure will work only if only tables refer the UDF.

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating