Alter a user defined datatype 1

[pgk]

Hi all,
I have created a datatype using the sp_addtype feature and this type (a Numberic value type) also is used in a lot of tables. I would like to alter this datatype to include a higher precision. Is it possible to do so without droping the tabes, the datatype and then recreating everything?

Thanks in advance.

Hope it helps. Let me know what happens.
With regards,
PGK

 

[sybaseguru]

PGK,

There is a workaround if you are using ASE 12.0 or over. It uses alter table command. BUT do not use alter table for ASE pre 12 it will cause problems.

OK let use see what we can do

--first I create a type
sp_addtype temp52, 'numeric(5,2)'
go
-- then I create a table
create table abc (col1 varchar(30) NOT NULL, col2 temp52 NOT NULL)
2> go
1> insert abc values('somethine', 123.25)
2> go
(1 row affected)
1> insert abc values('something2', 653.36)
2> go
(1 row affected)
1> select * from abc
2> go
col1 col2
------------------------------ --------
somethine 123.25
something2 653.36

--Now I want to change temp52 from numeric(5,2) to numeric(6,2). So I go and create a new type called temp62
1> sp_addtype temp62, 'numeric(6,2)'
2> go
Type added.
(return status = 0)
1> alter table abc modify col2 temp62
2> go
1> sp_help abc
2> go
Name Owner Object_type
------------------------------ ------------------------------ --------------------------------
abc dbo user table

(1 row affected)
Data_located_on_segment When_created
------------------------------ --------------------------
default Jun 5 2003 10:35AM
Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Identity
--------------- --------------- ----------- ---- ----- ----- --------------- --------------- ------------------------------ --------
col1 varchar 30 NULL NULL 0 NULL NULL NULL 0
col2 temp62 4 6 2 0 NULL NULL NULL 0

-- so my table has got the correct type but different name. All column values for col2 are adjusted as well.
-- This saves you from dropping and recreating the table.
-- Now you are free to drop the old type temp52(assuming that you are not using in sps and views as well)
1> sp_droptype temp52
2> go
Type has been dropped.
(return status = 0)
-- Now go and rename your new type with the old type name
1> sp_rename temp62, temp52
2> go
Name of user-defined type name changed.
1> sp_help abc
2> go
Name Owner Object_type
------------------------------ ------------------------------ --------------------------------
abc dbo user table

(1 row affected)
Data_located_on_segment When_created
------------------------------ --------------------------
default Jun 5 2003 10:35AM
Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Identity
--------------- --------------- ----------- ---- ----- ----- --------------- --------------- ------------------------------ --------
col1 varchar 30 NULL NULL 0 NULL NULL NULL 0
col2 temp52 4 6 2 0 NULL NULL NULL 0
-- OK table column name is automatically updated for you. Just make sure that you drop and recreate all the compiled objects (sps, views and triggers) which make a reference to this type should be dropped and recreated.

I hope this will help you

 

[pgk]

Hi SybaseGuru,
Thanks for the post. I have given youa star.

Hope it helps. Let me know what happens.
With regards,
PGK