How can I alter a 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.
Once you connect to a database in SSMS, you can view these data types by navigating to Programmability-> Types->System Data Types.
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'.
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):
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:
DROP
statements are before CREATE
statementsDROP
statements are in dependency order (i.e. reverse of CREATE)CREATE
statements are in dependency order
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With