Changing custom table types in SQL Server

Question

How can I change a custom table type in SQL Server?

Answer 1

As far as I know, it is not possible to change / change the table type. You can create a type with a different name and then delete the old type and change it to the new name

Jkrajes loans

According to msdn , this is similar to "A user-defined table type definition cannot be changed after it is created."

Answer 2

This is a kind of hack, but it seems to work. Below are the steps and an example of changing the type of a table. One note: sp_refreshsqlmodule will fail if the change you make to the table type is a violation of this object, usually a procedure.

• Use sp_rename to rename the table type, I usually just add z to the beginning of the name.
• Create a new table type with the original name and any changes you need to make a table type.
• Go through each dependency and run sp_refreshsqlmodule on it.
• Drop the renamed table type.

---

 EXEC sys.sp_rename 'dbo.MyTableType', 'zMyTableType'; GO CREATE TYPE dbo.MyTableType AS TABLE( Id INT NOT NULL, Name VARCHAR(255) NOT NULL ); GO DECLARE @Name NVARCHAR(776); DECLARE REF_CURSOR CURSOR FOR SELECT referencing_schema_name + '.' + referencing_entity_name FROM sys.dm_sql_referencing_entities('dbo.MyTableType', 'TYPE'); OPEN REF_CURSOR; FETCH NEXT FROM REF_CURSOR INTO @Name; WHILE (@@FETCH_STATUS = 0) BEGIN EXEC sys.sp_refreshsqlmodule @name = @Name; FETCH NEXT FROM REF_CURSOR INTO @Name; END; CLOSE REF_CURSOR; DEALLOCATE REF_CURSOR; GO DROP TYPE dbo.zMyTableType; GO 

Attention:

This can be damaging to your database, so you should check this out in the development environment first.

Answer 3

Here are simple steps that minimize boredom and don't require error-prone semi-automatic scripts or expensive tools.

Remember that you can generate DROP / CREATE statements for multiple objects from the Object Explorer Details window (in this case, the DROP and CREATE scripts are grouped, which makes it easier to insert logic between the Delete and Create actions):

1. Back up the database in case something goes wrong!
2. Automatically generate DROP / CREATE statements for all dependencies (or generate "Programmability" for all objects to eliminate the need to look for dependencies).
3. Between the DROP and CREATE [dependencies] statements (after all DROPs, before all CREATEs), insert the generated DROP / CREATE [table type] statements, making the necessary changes using CREATE TYPE.
4. Run a script that removes all / UDTT dependencies and then re-creates [UDTT with changes] / dependencies.

---

If you have small projects where it might make sense to change the infrastructure architecture, consider excluding custom table types. Entity Framework and similar tools allow you to move most, if not all, of the data logic into your code base, where it is easier to maintain.

Answer 4

If you can use the database project in Visual Studio, you can make changes to the project and compare the schema to synchronize the changes with your database.

Thus, resetting and restoring dependent objects is handled by modifying the script.

Answer 5

Simon Zeynstra found the solution!

But I used the Visual Studio 2015 community, and I didn't even have to use schema comparison.

Using SQL Server Object Browser, I found my user-defined table type in the database. I right-clicked on the type of table and selected. This opened a code tab in the IDE with visible TSQL code and editable . I just changed the definition (in my case, I just increased the size of the nvarchar field) and clicked the "Update Database" button in the upper left corner of the tab.

Hi Presto! - A quick check of SSMS and the definition of udtt has been changed.

Brilliant - thanks to Simon.

Answer 6

You cannot ALTER / MODIFY your TYPE. You need to discard the existing one and recreate it with the correct name / data type or add a new column / s

Answer 7

You must delete the old table type and create a new one. However, if it has any dependencies (any stored procedures using it), you cannot delete it. I posted another answer on how to automate the process of temporarily deleting all stored procedures, modifying a table of tables, and restoring stored procedures.