changing data type in userdef tabletype

Posted on 2013-10-18
Medium Priority
Last Modified: 2013-10-24
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.

Question by:25112
  • 2
LVL 66

Expert Comment

by:Jim Horn
ID: 39587608
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.

Author Comment

ID: 39588137
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 :(
LVL 66

Accepted Solution

Jim Horn earned 2000 total points
ID: 39588167
Yeah that could be pretty involved, and I'm guessing that there is a high potential for failure if objects dependant on a table that is defined as a type, and has it's column changed, causes downstream problems.

I don't have an immediate answer for you other than I'm not seeing any support docs that spell out how to alter a column in a data type, ergo 'You can't do that'.

So ... I'll back away from the question to encourage other experts to respond.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question