Alter User Defined Data Type

Question

Alter User Defined Data Type

ajnubee

Old Hand

Points: 348
More actions
March 5, 2013 at 12:23 pm

#271953

I'm trying to alter a column in a table that corresponds to UDDT, this column has a primary key and it reference to foriegn key in other tables. What should be the approach to accompolish this task. So many objects has dependencies on this UDDT

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

ajnubee (3/5/2013)
I'm trying to alter a column in a table that corresponds to UDDT, this column has a primary key and it reference to foriegn key in other tables. What should be the approach to accompolish this task. So many objects has dependencies on this UDDT

Welcome to SSC. It is hard to offer much advise here because you didn't offer many details.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323504 More actions · Answer 2

as far as i know, you cannot alter a user defined type.

you have to remove references to it, drop it, recreate it with a new definition, and then re-establish the items that used it previously(ie stored procs accepting a table type or column definition if it was a type )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

ajnubee (3/5/2013)
I'm trying to alter a column in a table that corresponds to UDDT, this column has a primary key and it reference to foriegn key in other tables. What should be the approach to accompolish this task. So many objects has dependencies on this UDDT

Sorry for the late post. Lowell provided the technical goods but I just wanted to add that it is sad but UDDTs are actually not worth implementing imho for this very reason. If they abstracted away the schema they would be great, i.e. if you could just alter a UDDT named FirstName from a VARCHAR(25) to a VARCHAR(50) and the engine just took care of all the places where that UDDT was used (tables, constraints, indexes, etc.) they would be uber-useful, but they do not work that way. The only thing they save us from having to do is modify stored code in procedures, functions and triggers which only makes them somewhat useful. I avoid implementing UDDTs mainly because the overhead associated with maintaining the active library of UDDTs in a given database in a large development team overwhelms their actual utility making them not worth the trouble.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato