Altering user-defined table types in SQL Server

Question

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

User · Answer

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

User · Answer

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

User · Answer

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       Back up you database in case anything goes wrong  Automatically generate the DROP CREATE statements for all dependencies  or generate for all  Programmability  objects to eliminate the tedium of finding dependencies   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  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

User · Answer

Just had to do this alter user defined table type in one of my projects   Here are the steps I employed   Find all the SP using the user defined table type  Save a create script for all the SP s  found  Drop the SP s   Save a create script for the user defined table you wish to alter  4 5 Add the additional column or changes you need to the user defined table type  Drop the user defined table type  Run the create script for the user defined table type  Run the create script for the SP s   Then start modifying the SP s  accordingly

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    Use sp rename to rename the table type  I typically just add z to the beginning of the name  Create a new table type with the original name and any modification you need to make to the table type  Step 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   WARNING   This can be destructive to your database  so you ll want to test this on a development environment first

[sql-server] Altering user-defined table types in SQL Server

Examples related to sql-server

Examples related to user-defined-types