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