Altering user-defined table types in SQL Server

Question

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

Answer 1

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

Answer 2

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.

1. Use sp_rename to rename the table type, I typically just add z to the beginning of the name.
2. Create a new table type with the original name and any modification you need to make to the table type.
3. Step through each dependency and run sp_refreshsqlmodule on it.
4. 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.

Answer 3

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

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

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

Just had to do this alter user defined table type in one of my projects. Here are the steps I employed:

1. Find all the SP using the user defined table type.
2. Save a create script for all the SP(s) found.
3. Drop the SP(s).
4. 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.
5. Drop the user defined table type.
6. Run the create script for the user defined table type.
7. Run the create script for the SP(s).
8. Then start modifying the SP(s) accordingly.

Answer 8

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

Answer 9

I created two stored procedures for this. The first one

create_or_alter_udt_preprocess takes the udt name as input, drops all the stored procs/functions that use the udt, drops the udt, and return a sql script to recreate all the procedures/functions.

The second one create_or_alter_udt_postprocess takes the script outputted from the first proc and executes it.

With the two procs, changing an udt can be done by:

1. call create_or_alter_udt_preprocess;
2. create the udt with a new definition;
3. call create_or_alter_udt_postprocess;

Use a transaction to avoid losing the original procs in case of errors.

create or ALTER   proc create_or_alter_udt_postprocess(@udt_postprocess_data xml)
as 
begin
    if @udt_postprocess_data is null 
        return;

    declare @obj_cursor cursor 
    set @obj_cursor = cursor fast_forward for 
    select n.c.value('.', 'nvarchar(max)') as definition
    from @udt_postprocess_data.nodes('/Objects/definition') as n(c)

    open @obj_cursor;

    declare @definition nvarchar(max);
    fetch next from @obj_cursor into @definition;
    while (@@fetch_status = 0)
    begin
        exec sp_executesql @stmt= @definition
        fetch next from @obj_cursor into @definition
    end

    CLOSE @obj_cursor;
    DEALLOCATE @obj_cursor; 
end

Create or ALTER   proc create_or_alter_udt_preprocess(@udt nvarchar(200), @udt_postprocess_data xml out) 
AS
    BEGIN
        set @udt_postprocess_data = null;
        if TYPE_ID(@udt) is null
            return;

        declare @drop_scripts nvarchar(max);
        SELECT @drop_scripts =  (
        (select N';'+ drop_script
            from 
        (
        SELECT 
            drop_script = N'drop ' + case sys.objects.type when 'P' then N'proc ' else N'function' end
                + sys.objects.name + N';' + + nchar(10) + nchar(13)
        FROM sys.sql_expression_dependencies d
        JOIN sys.sql_modules m ON m.object_id = d.referencing_id
        JOIN sys.objects ON sys.objects.object_id = m.object_id
        WHERE referenced_id = TYPE_ID(@udt)
        ) dependencies
        FOR XML PATH (''), type
        ).value('.', 'nvarchar(max)')
         ) ;
    
        declare @postprocess_data xml;

        set @udt_postprocess_data =
        (SELECT 
            definition
        FROM sys.sql_expression_dependencies d
        JOIN sys.sql_modules m ON m.object_id = d.referencing_id
        JOIN sys.objects ON sys.objects.object_id = m.object_id
        WHERE referenced_id = TYPE_ID(@udt)
        FOR XML PATH (''), root('Objects'));
        
        exec sp_executesql @stmt= @drop_scripts;
        exec sp_droptype @udt;
  END

Example usage:

begin tran
declare @udt_postprocess_data xml;

exec create_or_alter_udt_preprocess @udt= 'test_list', @udt_postprocess_data = @udt_postprocess_data out;
CREATE TYPE test_list AS TABLE(
    test_name nvarchar(50) NULL
);

exec create_or_alter_udt_postprocess @udt_postprocess_data = @udt_postprocess_data;

commit;

Code to set up the example usage:

CREATE TABLE [dbo].[test_table](
    [test_id] [int] IDENTITY(1,1) NOT NULL, [test_name] [varchar](20) NULL
) ON [USERDATA]
GO


CREATE TYPE test_list AS TABLE(test_name nvarchar(20) NULL)
GO

create proc add_tests(
@test_list test_list readonly)
as 
begin
    SET NOCOUNT ON;
    insert into test_table(test_name)
    select test_name
    from @test_list;
end;

create proc add_tests2(
@test_list test_list readonly)
as 
begin
    SET NOCOUNT ON;
    insert into test_table(test_name)
    select test_name
    from @test_list;
end;