How can I alter a user-defined table type in SQL Server ?
This question is related to
sql-server
user-defined-types
you cant ALTER/MODIFY your TYPE. You have to drop the existing and re-create it with correct name/datatype or add a new column/s
If you can use a Database project in Visual Studio, you can make your changes in the project and use schema compare to synchronize the changes to your database.
This way, dropping and recreating the dependent objects is handled by the change script.
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.
Simon Zeinstra has found the solution!
But, I used Visual Studio community 2015 and I didn't even have to use schema compare.
Using SQL Server Object Explorer, I found my user-defined table type in the DB. I right-mouse clicked on the table-type and selected . This opened a code tab in the IDE with the TSQL code visible and editable. I simply changed the definition (in my case just increased the size of an nvarchar field) and clicked the Update Database button in the top-left of the tab.
Hey Presto! - a quick check in SSMS and the udtt definition has been modified.
Brilliant - thanks Simon.
This is kind of a hack, but does seem to work. Below are the steps and an example of modifying a table type. One note is the sp_refreshsqlmodule will fail if the change you made to the table type is a breaking change to that object, typically a procedure.
sp_rename
to rename the table type, I typically just add z to
the beginning of the name.sp_refreshsqlmodule
on it.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
WARNING:
This can be destructive to your database, so you'll want to test this on a development environment first.
Just had to do this alter user defined table type in one of my projects. Here are the steps I employed:
You should drop the old table type and create a new one. However if it has any dependencies (any stored procedures using it) you won't be able to drop it. I've posted another answer on how to automate the process of temporary dropping all stored procedures, modifying the table table and then restoring the stored procedures.
Source: Stackoverflow.com