How to change a User Defined Data Type?

User defined data types or UDT's allow a database developer to define their own user defined data types over the base data types available in Sybase ASE. Adaptive Server Enterprise the enterprise version of RDBMS offered from Sybase Inc took care of developers requirement to define new data types via the user defined data types. Though a developer cannot completely define a new data type in Sybase but the developer can still define a customized version of data types to meet their customer requirements.

Lets say we use 250 character length too frequently to meet our customer requirements then we can define a user defined data type 'TEXT' to meet this requirement and use 'TEXT' instead of varchar(250) data type. A new data type can be defined via the following syntax -

sp_addtype TEXT, varchar(250), null

Apart from customizing the data type UDT's basic requirement is to provide a wrapper to the base data types so that in case the below mentioned data type gets changed the UDT remains the same. However, this statement has a major catch -

The catch is that once we define a column specific to a UDT Sybase does not provide any option to make amendments to this UDT and hence there is nothing like - sp_modifytype which will help us change the UDT - What if we need to amend our TEXT (varchar(250)) to TEXT (i.e. varchar(255))?

So, what to do in this scenario?

Well there isn't a straight forward answer to this problem as already said there is no system stored procedure like sp_modifytype defined by Sybase Adaptive Server Enterprise. But we do have a round about method to help us get out of this situation. The steps to follow isn't pretty simple but will help us change our UDT. The steps are as follows -

1. Use sp_rename system stored procedure to rename the pre-existing TEXT UDT to TEXT1. This will help maintain the reference for TEXT UDT in the pre-existing tables in our database and we won't encounter error that the UDT is in use.

2. Now create a new UDT using sp_addtype named as TEXT with base data type as varchar(255). This is the datatype which needs to be correctly referenced in all the tables using TEXT1 now.

3. Now we need to find out all the tables using the existing UDT. This can be done by querying the syscolumns table by making a join over the id column with sysobjects system table.

4. Once we get the list of all the tables using the previous UDT, we need to change the reference of this UDT with the new TEXT UDT. How? Here we need to change the User Type Id for the new data type defined (varchar(255)). This will change the reference to the new UDT rather than the previous UDT. Now you might be wondering where to pick up the User Type Id then this needs to be taken from the systypes system table.

5. Drop the renamed TEXT1 UDT.

Well this is not all as this will work in case of simple scenario's because of the length and the offset values so in more complex cases we have no alternative but to drop and recreate the UDT.