Is it possible to change a user-defined table type?

Question

Is it possible to change a user-defined table type?

Navigation

#1 by (3 votes)

1

I created a table type to use in some procedures, and after creating the procedures, I realized that I need to change some of the types of columns.

Is there any instruction to change a table type?

I'm trying to avoid deleting the type and create it again to avoid problems with scripting order ... I tried dropping and creating again, but since there are procedures that use this type, SQL Server prevents the script from running.

CREATE TYPE [dbo].[MeuTipo] AS TABLE(
[Campo1] [int] NULL,
[Campo2] [varchar](512) NULL,
[Campo3] [datetime] NULL,
[Campo4] [varchar](512) NULL,
[Campo5] [bigint] NULL)
GO

sql-server

asked by anonymous 27.09.2017 / 22:03

1 answer

How to add foreign key with constraint and alter table in Oracle? How to make animation occur several times

score 3 · Accepted Answer

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