在 SQL Server 中更改用户定义的类型 [英] Alter user defined type in SQL Server

问题描述

我在我的数据库中创建了几个用户定义的类型，如下所示

I created few user defined types in my database as below

CREATE TYPE [dbo].[StringID] FROM [nvarchar](20) NOT NULL

并将它们分配到各种表.我的数据库中的表采用各种模式(不仅是 dbo)

and assigned them to various tables. The tables in my database are in various schemas (not only dbo)

但我意识到我需要更大的领域，我需要改变，例如从 [nvarchar](20) 增加到 [nvarchar](50)，但是有不是 ALTER TYPE 语句.

But I realized I need bigger field, and I need to alter, e.g increase from [nvarchar](20) to [nvarchar](50), but there is no ALTER TYPE statement.

我需要一个使用临时表/光标的脚本，并保存使用我的类型的所有表和字段.然后将现有字段更改为基本类型 - 例如从 CustID [StringID] 到 CustID [nvarchar(20)].删除用户类型并使用新类型重新创建它 - 例如nvarchar(50)最后将字段设置回用户类型

I need a script that uses a temp table/cursor whatever and saves all the tables and fields where my type is used. Then change existing fields to base type - e.g. from CustID [StringID] to CustID [nvarchar(20)]. Drop the user type and recreate it with new type - e.g. nvarchar(50) and finally set back fields to user type

我没有在类型上定义规则，所以不必删除规则并重新添加它们.

I do not have rules defined on types, so don't have to drop rules and re-add them.

感谢任何帮助.

推荐答案

这是我通常使用的，虽然有点手动:

This is what I normally use, albeit a bit manual:

/* Add a 'temporary' UDDT with the new definition */ 
exec sp_addtype t_myudt_tmp, 'numeric(18,5)', NULL 


/* Build a command to alter all the existing columns - cut and 
** paste the output, then run it */ 
select 'alter table dbo.' + TABLE_NAME + 
       ' alter column ' + COLUMN_NAME + ' t_myudt_tmp' 
from INFORMATION_SCHEMA.COLUMNS 
where DOMAIN_NAME = 't_myudt' 

/* Remove the old UDDT */ 
exec sp_droptype t_mydut


/* Rename the 'temporary' UDDT to the correct name */ 
exec sp_rename 't_myudt_tmp', 't_myudt', 'USERDATATYPE' 

这篇关于在 SQL Server 中更改用户定义的类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！