Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Altering user-defined table types in SQL Server

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

like image 640
yogi Avatar asked Jul 10 '12 09:07

yogi


People also ask

Can we alter user-defined table type in SQL Server?

Since the advent of table-valued parameters in SQL Server 2008, table types have become more and more popular. Unfortunately, once a table type is actively being referenced by one or more objects, it is cumbersome to change. There is no ALTER TYPE, and you can't drop and re-create a type that is in use.

How can check user-defined table type in SQL Server?

Once you connect to a database in SSMS, you can view these data types by navigating to Programmability-> Types->System Data Types.


2 Answers

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'.

like image 169
Manivannan Nagarajan Avatar answered Sep 28 '22 00:09

Manivannan Nagarajan


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):

Drop and Create To

  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

screenshot showing how to generate drop create statements for multiple objects

like image 34
lightmotive Avatar answered Sep 28 '22 02:09

lightmotive