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

Asked

Viewed 79 times

1

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

Is there any instruction to change a table type?

I’m trying to avoid deleting the type and create again to avoid problems with script execution order... I tried to drop and create again, but as there are procedures that use this type, SQL Server prevents the execution of the script.

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

  • Can you put the type creation script to check?

  • I added it as an edit

  • And what do you want to change in this kind?

  • The change in my case would be the length of the varchar of one of the columns, but Xting my question to other changes as well, such as new columns/column removal etc.

1 answer

3


There is no possibility to change the type directly. What you can do is use the sp_rename to rename the type with a temporary name, re-create it with the new settings and update the dependencies using the 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 Microsoft user-defined table, index, column, alias data type or CLR data type. NET Framework Common Language Runtime.

sp_refreshsqlmodule

Updates the metadata of the stored procedure not associated with schema, the user-defined function, the display, the DML trigger, the database-level DDL trigger, or the server-level DDL trigger specified in the current database. Persistent metadata of these objects, such as parameter data types, may become outdated due to updates to their underlying objects.

Reference: Altering user-defined table types in SQL Server

Browser other questions tagged database sql-server

You are not signed in. Login or sign up in order to post.