| Author |
Topic |
|
ereader
Yak Posting Veteran
50 Posts |
 Posted - 2003-09-09 : 05:58:30
|
| hello all,#################################################EXEC sp_addtype phone, 'VARCHAR(15)', 'NOT NULL'#################################################we have created the above UDDT and we have used the same data type in more than 13 tables now I want to increase it;s length to varchar(20)===> can we do it using systypes???????????????How Can I do it ? pls. suggest |
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-09-09 : 07:50:28
|
| Interesting problem. As you know, you can not drop the data type if it is being used by other tables. There is no SPROC that allows you to modify your user defined data type.The solution that I think will work is as follows :ALTER TABLE yourTable ALTER COLUMN yourColumn [change datatype to VARCHAR instead of your UDDT]drop your UDDT, EXEC sp_droptypeRe-create your UDDT and then use the above ALTER command to change it back to your newly defined data type.As for the systypes table, it's best left alone. You can't run updates on the table anyway, unless you request it from your DBA that is.... and one more thing. I just found out that you can not use a UDDT when creating a temp table in SQL Server. I wonder why?!------------------------------------------------------------------------------I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!! |
 |
|
|
ereader
Yak Posting Veteran
50 Posts |
Posted - 2003-09-09 : 09:09:32
|
well Amethystium
#############################################Interesting problem. As you know, you can not drop the data type if it is being used by other tables. There is no SPROC that allows you to modify your user defined data type.As for the systypes table, it's best left alone. You can't run updates on the table anyway, unless you request it from your DBA that is. ###############################################that;s the same alternative i was thinking but can u tell meHow can we update the systypes system table if I'll enable the allow system catalog update ?can we do it with a sa logintnx |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-09-09 : 10:07:18
|
quote:
How can we update the systypes system table if I'll enable the allow system catalog update ?can we do it with a sa login
Yup, but the DBAs will need to know what you're up to! ------------------------------------------------------------------------------I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-09-09 : 22:36:45
|
| DO NOT MODIFY THE SYSTEM TABLES DIRECTLY!!!Seriously, in the time you've spent typing the question and waiting for the answer, you could've gotten the job done. Trying to find a dangerous shortcut is not worth it, you'll FUBAR your database that way.In order to use user-defined data types in temp tables, you have to create them in tempdb. You can make this easier by creating the data types you want in the model database. Tempdb is created from model every time you restart SQL Server, and model also is used as a template when creating new databases. The same applies to tables, views, rules, and user accounts that you wish to have available in all databases. |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2003-09-10 : 00:04:01
|
| I'm not sure what you're saying Rob. Isn't turning allow_updates to on the first thing a consultant should always do? ;)But seriously though, I picking up little things like adding stuff to model to get stuff into tempdb is why I keep coming back here.----------------------"O Theos mou! Echo ten labrida en te mou kephale!""Where theres a will, theres a kludge." - Ken Henderson |
|
|
|
ereader
Yak Posting Veteran
50 Posts |
Posted - 2003-09-10 : 03:59:30
|
| Thanks everybody, But still CAN UDDT BE MODIFIED in a safest way? Please advise. Becuase our problem is we are using UDDT in more than 20 tables and many other stored procedures and sql functions.But now we want to alter the UDDT with minimum down time on production DB.Thanks in advanceereader |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-09-10 : 04:43:07
|
| To be honest, doing ALTER TABLE ALTER COLUMN in a big script is going to be relatively fast and safe. I think the impression that Rob was trying to give is that trying to do this by playing with the system tables is a bad idea.I imagine that if altering User Defined Datatypes whilst they are in use is a good idea, then there would be a function to do it.-------Moo. :) |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-09-10 : 09:13:18
|
quote:
Originally posted by ereader
Thanks everybody, But still CAN UDDT BE MODIFIED in a safest way? Please advise. Becuase our problem is we are using UDDT in more than 20 tables and many other stored procedures and sql functions.But now we want to alter the UDDT with minimum down time on production DB.Thanks in advanceereader
Looks like there isn't another method, which means you've got 20 odd tables to ALTER  __________________Make love not war! |
|
|
|
ereader
Yak Posting Veteran
50 Posts |
Posted - 2003-09-12 : 09:28:28
|
| Thanks all of you for your generous effort |
|
|
|
|
User Defined Data Type Modification