There is no possibility of directly changing the type. What you can do is to use sp_rename
to rename the type with a temporary name, rebuild it with new definitions, and update dependencies using procedure
sp_refreshsqlmodule
:
-- Renomeia o tipo
EXEC sys.sp_rename 'dbo.MeuTipo', 'MeuTipoTEMP';
GO
-- Cria o tipo com as novas definições
CREATE TYPE dbo.MeuTipo AS TABLE(
Campo1 INT NULL,
Campo2 VARCHAR(512) NULL,
Campo3 DATETIME NULL,
Campo4 VARCHAR(512) NULL,
Campo5 BIGINT NULL
);
GO
-- Percorre as dependências atualizando-as
DECLARE @Nome NVARCHAR(1000);
DECLARE cursor_referencias CURSOR FOR
SELECT referencing_schema_name + '.' + referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.MeuTipo', 'MeuTipoTEMP');
OPEN cursor_referencias;
FETCH NEXT FROM cursor_referencias INTO @Nome;
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC sys.sp_refreshsqlmodule @Name = @Nome;
FETCH NEXT FROM cursor_referencias INTO @Nome;
END;
CLOSE cursor_referencias;
DEALLOCATE cursor_referencias;
GO
DROP TYPE MeuTipoTEMP;
GO
sp_rename
Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or user-defined CLR data type of the Microsoft .NET Framework Common Language Runtime.
sp_refreshsqlmodule
Updates stored procedure metadata not associated with schema, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger specified in the current database . Persistent metadata for these objects, such as parameter data types, may be out of date due to updates to their underlying objects.
Reference: Altering user -defined table types in SQL Server