Is there a command to change a user-created type in Sql Server?

2

I created a type in my Sql Server database:

CREATE TYPE [dtNumero] FROM [numeric](18, 5) NOT NULL

Is there a command for you to change this type, for example precision?

    
asked by anonymous 14.11.2014 / 12:17

1 answer

2

Can not directly change the structure of type (something like ALTER TYPE ).

What is possible is to create a temporary type with a new definition, change all tables with that field in your database, and finally rename the new type created:

/* Adiciona um novo UUDT 'temporário' com a nova definição */ 
exec sp_addtype t_dtNumero_tmp, 'numeric(18,5)', NULL 

/* Cria um alter table para todas as  tabelas que usam o tipo que será alterado
 * Copie e cole o resultado gerado */ 
SELECT 'ALTER TABLE dbo.' + TABLE_NAME + 
       ' ALTER COLUMN ' + COLUMN_NAME + ' t_dtNumero_tmp' 
from INFORMATION_SCHEMA.COLUMNS 
where DOMAIN_NAME = 't_dtNumero' 

/* Exclui o tipo antigo */ 
exec sp_droptype t_dtNumero

/* Renomeia o tipo temporario para o nome antigo */ 
exec sp_rename 't_dtNumero_tmp', 't_dtNumero', 'USERDATATYPE'

Based on the following SOen response: link / p>     

14.11.2014 / 12:43