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

Question

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 the accuracy?

Answer 1

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

What is possible is to create a temporary type with a new definition, change all tables with this 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: https://stackoverflow.com/a/1383509/2099835}