我在数据库中创建了几个用户定义的类型，如下所示
CREATE TYPE [dbo].[StringID] FROM [nvarchar](20) NOT NULL
我已经将它们分配给各个表。我的数据库中的表位于不同的模式中（不仅限于 dbo）
但是我意识到我需要更大的字段，需要进行更改，例如从 [nvarchar]（20） 增加到 [nvarchar]（50），但是没有 ALTER TYPE 语句。
我需要一个脚本，使用临时表/光标等保存使用我的类型的所有表和字段。然后将现有字段更改为基本类型 - 例如从 CustID [StringID] 更改为 CustID [nvarchar(20)]。
删除用户自定义类型并使用新类型重新创建它 - 例如 nvarchar(50)，
最后将字段设置回用户自定义类型。
我没有定义类型的规则，因此不必删除规则并重新添加它们。
任何帮助都将不胜感激。
这是我通常使用的方法，虽然有点手动:
/* 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'
exec RECREATE_TYPE @schema='dbo', @typ_nme='typ_foo', @sql='AS TABLE([bar] varchar(10) NOT NULL)'
代码：
CREATE PROCEDURE [dbo].[RECREATE_TYPE]
@schema VARCHAR(100), -- the schema name for the existing type
@typ_nme VARCHAR(128), -- the type-name (without schema name)
@sql VARCHAR(MAX) -- the SQL to create a type WITHOUT the "CREATE TYPE schema.typename" part
AS DECLARE
@scid BIGINT,
@typ_id BIGINT,
@temp_nme VARCHAR(1000),
@msg VARCHAR(200)
BEGIN
-- find the existing type by schema and name
SELECT @scid = [SCHEMA_ID] FROM sys.schemas WHERE UPPER(name) = UPPER(@schema);
IF (@scid IS NULL) BEGIN
SET @msg = 'Schema ''' + @schema + ''' not found.';
RAISERROR (@msg, 1, 0);
END;
SELECT @typ_id = system_type_id FROM sys.types WHERE UPPER(name) = UPPER(@typ_nme);
SET @temp_nme = @typ_nme + '_rcrt'; -- temporary name for the existing type
-- if the type-to-be-recreated actually exists, then rename it (give it a temporary name)
-- if it doesn't exist, then that's OK, too.
IF (@typ_id IS NOT NULL) BEGIN
exec sp_rename @objname=@typ_nme, @newname= @temp_nme, @objtype='USERDATATYPE'
END;
-- now create the new type
SET @sql = 'CREATE TYPE ' + @schema + '.' + @typ_nme + ' ' + @sql;
exec sp_sqlexec @sql;
-- if we are RE-creating a type (as opposed to just creating a brand-spanking-new type)...
IF (@typ_id IS NOT NULL) BEGIN
exec recompile_prog; -- then recompile all stored procs (that may have used the type)
exec sp_droptype @typename=@temp_nme; -- and drop the temporary type which is now no longer referenced
END;
END
GO
CREATE PROCEDURE [dbo].[recompile_prog]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @v TABLE (RecID INT IDENTITY(1,1), spname sysname)
-- retrieve the list of stored procedures
INSERT INTO
@v(spname)
SELECT
'[' + s.[name] + '].[' + items.name + ']'
FROM
(SELECT sp.name, sp.schema_id, sp.is_ms_shipped FROM sys.procedures sp UNION SELECT so.name, so.SCHEMA_ID, so.is_ms_shipped FROM sys.objects so WHERE so.type_desc LIKE '%FUNCTION%') items
INNER JOIN sys.schemas s ON s.schema_id = items.schema_id
WHERE is_ms_shipped = 0;
-- counter variables
DECLARE @cnt INT, @Tot INT;
SELECT @cnt = 1;
SELECT @Tot = COUNT(*) FROM @v;
DECLARE @spname sysname
-- start the loop
WHILE @Cnt <= @Tot BEGIN
SELECT @spname = spname
FROM @v
WHERE RecID = @Cnt;
--PRINT 'refreshing...' + @spname
BEGIN TRY -- refresh the stored procedure
EXEC sp_refreshsqlmodule @spname
END TRY
BEGIN CATCH
PRINT 'Validation failed for : ' + @spname + ', Error:' + ERROR_MESSAGE();
END CATCH
SET @Cnt = @cnt + 1;
END;
END
值得注意的是，如果您有任何视图，此脚本将包括这些视图。我运行了它，而不是执行内联生成的脚本，而是生成了一个脚本作为输出，然后进行了调整和运行。
另外，如果您有使用用户定义类型的函数/存储过程，则需要在运行脚本之前删除它们。
教训：将来不要费心使用UDT，它们带来的麻烦比它们的价值还大。
SET NOCOUNT ON
DECLARE @udt VARCHAR(150)
DECLARE @udtschema VARCHAR(150)
DECLARE @newudtschema VARCHAR(150)
DECLARE @newudtDataType VARCHAR(150)
DECLARE @newudtDataSize smallint
DECLARE @OtherParameter VARCHAR(50)
SET @udt = 'Name' -- Existing UDDT
SET @udtschema = 'dbo' -- Schema of the UDDT
SET @newudtDataType = 'varchar' -- Data type for te new UDDT
SET @newudtDataSize = 500 -- Lenght of the new UDDT
SET @newudtschema = 'dbo' -- Schema of the new UDDT
SET @OtherParameter = ' NULL' -- Other parameters like NULL , NOT NULL
DECLARE @Datatype VARCHAR(50),
@Datasize SMALLINT
DECLARE @varcharDataType VARCHAR(50)
DECLARE @Schemaname VARCHAR(50),
@TableName VARCHAR(50),
@FiledName VARCHAR(50)
CREATE TABLE #udtflds
(
Schemaname VARCHAR(50),
TableName VARCHAR(50),
FiledName VARCHAR(50)
)
SELECT TOP 1
@Datatype = Data_type,
@Datasize = character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Domain_name = @udt
AND Domain_schema = @udtschema
SET @varcharDataType = @Datatype
IF @DataType Like '%char%'
AND @Datasize IS NOT NULL
AND ( @newudtDataType <> 'varchar(max)'
OR @newudtDataType <> 'nvarchar(max)'
)
BEGIN
SET @varcharDataType = @varcharDataType + '('
+ CAST(@Datasize AS VARCHAR(50)) + ')'
END
INSERT INTO #udtflds
SELECT TABLE_SCHEMA,
TABLE_NAME,
Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Domain_name = @udt
AND Domain_schema = @udtschema
DECLARE @exec VARCHAR(500)
DECLARE alter_cursor CURSOR
FOR SELECT Schemaname,
TableName,
FiledName
FROM #udtflds
OPEN alter_cursor
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName
+ ' ALTER COLUMN ' + @FiledName + ' ' + @varcharDataType
EXECUTE ( @exec
)
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName
END
CLOSE alter_cursor
SET @exec = 'DROP TYPE [' + @udtschema + '].[' + @udt + ']'
EXEC ( @exec
)
SET @varcharDataType = @newudtDataType
IF @newudtDataType Like '%char%'
AND @newudtDataSize IS NOT NULL
AND ( @newudtDataType <> 'varchar(max)'
OR @newudtDataType <> 'nvarchar(max)'
)
BEGIN
SET @varcharDataType = @varcharDataType + '('
+ CAST(@newudtDataSize AS VARCHAR(50)) + ')'
END
SET @exec = 'CREATE TYPE [' + @newudtschema + '].[' + @udt + '] FROM '
+ @varcharDataType + ' ' + @OtherParameter
EXEC ( @exec
)
OPEN alter_cursor
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName
+ ' ALTER COLUMN ' + @FiledName + ' ' + '[' + @newudtschema
+ '].[' + @udt + ']'
EXECUTE ( @exec
)
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName
END
CLOSE alter_cursor
DEALLOCATE alter_cursor
SELECT *
FROM #udtflds
DROP TABLE #udtflds
1：http://www.sql-server-performance.com/2008/how-to-alter-a-uddt/已取代http://www.sql-server-performance.com/faq/How_to_alter_a%20_UDDT_p1.aspx
正如devio所说，如果UDT正在使用中，则无法简单地编辑它。
通过SMS的解决方法对我有用，这种方式是生成一个创建脚本并进行适当更改；重新命名现有的UDT；运行创建脚本；重新编译相关存储过程并删除已重新命名的版本。
/* Rename the UDDT you want to replace to another name */
exec sp_rename 'PrizeType', 'PrizeTypeOld', 'USERDATATYPE';
/* Add the updated UDDT with the new definition */
CREATE TYPE [dbo].[PrizeType] AS TABLE(
[Type] [nvarchar](50) NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[ImageUrl] [varchar](max) NULL
);
/* We need to force stored procedures to refresh with the new type... let's take care of that. */
/* Get a cursor over a list of all the stored procedures that may use this and refresh them */
declare sprocs cursor
local static read_only forward_only
for
select specific_name from information_schema.routines where routine_type = 'PROCEDURE'
declare @sprocName varchar(max)
open sprocs
fetch next from sprocs into @sprocName
while @@fetch_status = 0
begin
print 'Updating ' + @sprocName;
exec sp_refreshsqlmodule @sprocName
fetch next from sprocs into @sprocName
end
close sprocs
deallocate sprocs
/* Drop the old type, now that everything's been re-assigned; must do this last */
drop type PrizeTypeOld;
我尝试解决类似问题ALTERing XML SCHEMA COLLECTIONS，这些步骤似乎大多适用于ALTER TYPE。感谢您提交此建议，但考虑到其优先级相对于我们队列中的许多其他项目，我们不太可能真正完成它。因此，我们将此建议关闭为“不修复”。
对一个老问题的新回答：
Visual Studio 的数据库项目在部署更改时会处理删除和重建过程。它将删除使用 UDDT 的存储过程，然后在删除和重建数据类型后重新创建它们。
1. 重命名旧的UDT，
2. 执行查询，
3. 删除旧的UDT。
先简单执行 DROP TYPE，然后进行更正/修改后再次执行 CREATE TYPE？
有一个简单的测试可以查看它是否已被定义，就像表格、存储过程或函数一样 - 如果我不在工作中，我会查找这是什么？
（我只是粗略地浏览了上面的内容...如果我读错了，提前道歉！;)
sp_addtype和sp_droptype自SQL Server 2005起已被弃用。 （注：该提示提醒用户sp_addtype和sp_droptype这两个SQL Server操作已经过时，不再建议使用） - Jon Seigel