25112
asked on
changing data type in userdef tabletype
for changing type in table, it is easy:
alter table table1 alter column column1 smallint not null
but it is not straight forward in user defined table type..
(CREATE TYPE abc AS TABLE(column list)
right now to change the data type in the column list within the table type, we have to drop the defined data type and add it back.. if there are any dependencies, they need to be dropped also..
if you have any suggestions to simplify this, when you have to change a type, please share.
thanks
alter table table1 alter column column1 smallint not null
but it is not straight forward in user defined table type..
(CREATE TYPE abc AS TABLE(column list)
right now to change the data type in the column list within the table type, we have to drop the defined data type and add it back.. if there are any dependencies, they need to be dropped also..
if you have any suggestions to simplify this, when you have to change a type, please share.
thanks
Please explain why dropping and re-creating the user-defined table type would cause a problem. Chances are this may not be a good programming practice.
ASKER
jimhorn, when you have to change for many such instances across the database and there are many code objects dependent, so you have to first drop the functions and then drop the table types, then readd the function and then the table types.. so much to just change a data type :(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.