Changing length of User-Defined Datatype
-
AFAIK, there is no standard way of changing a user-defined datatype. In some way, this is logical, since changing the base type would give a lot of problems...
However, I am in a situation where I want to extend the length of a varchar UDT from 50 to 100. I cannot see any problem in this, since existing data will not be any problem in the database.
Is it safe to just change the length in systypes? Or is the only way really to change all columns and parameters and whatever to a new type, drop the original one and rename the new type?
-
Heres what I think you could do (kinda like musical chairs):
Scenraio A (want to keep the UDT name the same)
1) Create a new temp type that is the same size as the old type
2) Change all tables using the old type to the new temp type
3) Drop the old type, and re-create it with the new size
4) Change all the tables to using the new type.
5) Drop the temp type
Scenraio B (do not care if you keep the UDT name the same)
1) Create a new type that is the new size as desired
2) Change all tables using the old type to the new type
5) Drop the old type
Tim C.
//Will write code for food
Tim C //Will code for food -
In Re: "Is it safe to just change the length in systypes?"
I would never make changes to the system tables directly.
One path to consider is to run Profiler while making this type of change using EM. That will show you the steps necessary.
HTH
Steve Hendricks
MCSD, MCDBA
AFS Consulting Group
(949) 588-9800 x15
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15 -
Thought of that, but EM does not allow you to change the length of a UDT. So no luck there.
The solution posted by Tim is the only one I found so far, but taking into account that our UDT is used in at least 7 tables and between 20 and 30 stored procedures does not make this process appealing.
-
Actually I think it is good that there is no way to do this, other than those suggested by Tim. The reason why you use an UDT in the first place is because it means something. If you then later on change that definition, how would you know that this won't affect someone elses tables, sprocs etc? In your specific case you may have total control of the database and all, but in database theory you don't. So I would go with Tim's second suggestion, though I would probably not drop the old one as it has once been defined to mean something. This way you make sure that all instances of this UDT changed really do want it to mean whatever the new definition means.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
I can see your point Chris. Changing the DB layout can cause a lot of harm when they are referenced. But as you mention we do have full control over the database, with only our own application accessing it. So there's no risk of breaking anything when changing the length.
On the other hand, it seems odd to me that a procedure exists to rename an object (sp_rename), which would probably break more stuff than increasing the length of a UDT.
So it seems that our friends at MS haven't been too consistent in implementing advanced features
-
Absolutely, good point about sp_rename. What could have been inlcuded (even though it might break stuff, just as sp_rename) is a system proc for sysadmin use only that changes UDTs. sqlwish@microsoft.com perhaps?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Talking about the joys of sp_rename, I just love sp_renamedb especially when doing cross database querying, and someone decides to change the DB name on you because a client wants to call the application a different name, so they rename the DB too! YEEE HAAAA!!! Add water, instant breakage....
Sorry, the above was a small off post rant. But I feel better now.
Tim C.
//Will write code for food
Tim C //Will code for food -
Tim, you really brought up some bad memories there...
To continue your off-topic rant, I just remembered an old project where the customer decided that 'Articles' should no longer be called 'Articles' the system, but be changed to 'Products'. This decision came when the database had been in production for months already! I tried to explain to them that we could change it in the GUI but there was no need to change the names of database objects, but they didn't go with that and instead made us change all database objects referencing the table Articles (which changed name to Products accordingly). Sigh...
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Been there, done that ...
Once had a client who insisted that we changed all names in our database to reflect their own database naming conventions.
At least we managed to charge some big buck$ for that...
-
OMG, just love hearing that the JOYS
that I have gone through are shared elsewhere. Misery loves company.Tim C.
//Will write code for food
Tim C //Will code for food
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply