Altering user-defined table types in SQL Server

Question

How can I alter a user-defined table type in SQL Server ?

Answer 1

As of my knowledge it is impossible to alter/modify a table type.You can create the type with a different name and then drop the old type and modify it to the new name

Credits to jkrajes

As per msdn, it is like 'The user-defined table type definition cannot be modified after it is created'.

Answer 2

Here are simple steps that minimize tedium and don't require error-prone semi-automated scripts or pricey tools.

Keep in mind that you can generate DROP/CREATE statements for multiple objects from the Object Explorer Details window (when generated this way, DROP and CREATE scripts are grouped, which makes it easy to insert logic between Drop and Create actions):

1. Back up you database in case anything goes wrong!
2. Automatically generate the DROP/CREATE statements for all dependencies (or generate for all "Programmability" objects to eliminate the tedium of finding dependencies).
3. Between the DROP and CREATE [dependencies] statements (after all DROP, before all CREATE), insert generated DROP/CREATE [table type] statements, making the changes you need with CREATE TYPE.
4. Run the script, which drops all dependencies/UDTTs and then recreates [UDTTs with alterations]/dependencies.

---

If you have smaller projects where it might make sense to change the infrastructure architecture, consider eliminating user-defined table types. Entity Framework and similar tools allow you to move most, if not all, of your data logic to your code base where it's easier to maintain.

To generate the DROP/CREATE statements for multiple objects, you can right-click your Database > Tasks > Generate Scripts... (as shown in the screenshot below). Notice:

1. DROP statements are before CREATE statements
2. DROP statements are in dependency order (i.e. reverse of CREATE)
3. CREATE statements are in dependency order