在 Sql Server 中更改用户定义的类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1383494/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alter user defined type in Sql Server
提问by bzamfir
I created few user defined types in DB as below
我在 DB 中创建了几个用户定义的类型，如下所示
CREATE TYPE [dbo].[StringID] FROM [nvarchar](20) NOT NULL
and assigned to various tables. My tables in db are in various schemas (not only dbo)
并分配到各种表。我在 db 中的表采用各种模式（不仅是 dbo）
But I realized I need bigger field, and I need to alter, e.g increase from nvarchar to nvarchar, but there is no ALTER TYPE statement
但我意识到我需要更大的领域，我需要改变，例如从 nvarchar 增加到 nvarchar，但没有 ALTER TYPE 语句
I need a script that temp table/ cursor whatever and save there all 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
我需要一个临时表/光标的脚本，并将所有使用我的类型的表和字段保存在那里。然后将现有字段更改为基本类型 - 例如从 CustID [StringID] 到 CustID [nvarchar(20)]。删除用户类型并使用新类型重新创建它 - 例如 nvarchar(50) 并最终将字段设置回用户类型
I do not have rules define on types, so don't have to drop rules and re-add them
我没有在类型上定义规则，所以不必删除规则并重新添加它们
I'm not very familiar with T-Sql, so any help is appreciated.
我对 T-Sql 不是很熟悉，所以任何帮助表示赞赏。
采纳答案by Philip Fourie
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'
回答by Konektiv
We are using the following procedure, it allows us to re-create a type from scratch, which is "a start". It renames the existing type, creates the type, recompiles stored procs and then drops the old type. This takes care of scenarios where simply dropping the old type-definition fails due to references to that type.
我们正在使用以下程序，它允许我们从头开始重新创建一个类型，这是“一个开始”。它重命名现有类型，创建类型，重新编译存储的过程，然后删除旧类型。这会处理由于引用该类型而导致简单地删除旧的类型定义失败的情况。
Usage Example:
用法示例：
exec RECREATE_TYPE @schema='dbo', @typ_nme='typ_foo', @sql='AS TABLE([bar] varchar(10) NOT NULL)'
Code:
代码：
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
回答by Robin
there's a good example of a more comprehensive script here
It's worth noting that this script will include views if you have any. I ran it and instead of exec'ing inline generated a script as the output which I then tweaked and ran.
值得注意的是，如果您有视图，此脚本将包含视图。我运行它，而不是 exec'ing inline 生成一个脚本作为输出，然后我调整并运行它。
Also, if you have functions/sprocs using the user defeined types you'll need to drop those before running your script.
此外，如果您有使用用户定义类型的函数/过程，您需要在运行脚本之前删除它们。
Lesson Learned:in future, don't bother with UDTs they're more hassle than they're worth.
经验教训：将来，不要为 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/has replaced http://www.sql-server-performance.com/faq/How_to_alter_a%20_UDDT_p1.aspx
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
回答by Pete
As devio says there is no way to simply edit a UDT if it's in use.
正如devio所说，如果正在使用UDT，则无法简单地对其进行编辑。
A work-round through SMS that worked for me was to generate a create script and make the appropriate changes; rename the existing UDT; run the create script; recompile the related sprocs and drop the renamed version.
通过 SMS 对我有用的一个解决方法是生成一个创建脚本并进行适当的更改；重命名现有的 UDT；运行创建脚本；重新编译相关的 sproc 并删除重命名的版本。
回答by devio
The solutions provided here can only be applied if the user defined types are used in table definitions only, and if the UDT columns are not indexed.
此处提供的解决方案仅适用于仅在表定义中使用用户定义类型且未对 UDT 列编制索引的情况。
Some developers also have SP's and functions using UDT parameters, which is not covered either. (see comments on Robin's linkand in the Connect entry)
一些开发人员也有使用 UDT 参数的 SP 和函数，这也不包括在内。（请参阅Robin 链接和Connect 条目中的评论）
The Connect entry from 2007 has finally been closed after 3 years:
2007 年的 Connect 条目在 3 年后终于关闭了：
Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won't fix”.
感谢您提交此建议，但考虑到它相对于我们队列中的许多其他项目的优先级，我们不太可能真正完成它。因此，我们以“无法解决”为由关闭此建议。
I tried to solve a similiar problem ALTERing XML SCHEMA COLLECTIONS, and the steps seem to mostly apply to ALTER TYPE, too:
我试图解决一个类似的问题ALTERing XML SCHEMA COLLECTIONS，这些步骤似乎也主要适用于 ALTER TYPE：
To drop a UDT, the following steps are necessary:
要删除 UDT，需要执行以下步骤：
- If a table column references the UDT, it has to be converted to the underlying type
- If the table column has a default constraint, drop the default constraint
- If a procedure or function has UDT parameters, the procedure or function has to be dropped
- If there is an index on a UDT column, the index has to be dropped
- If the index is a primary key, all foreign keys have to be dropped
- If there are computed columns based on a UDT column, the computed columns have to be dropped
- If there are indexes on these computed columns, the indexes have to be dropped
- If there are schema-bound views, functions, or procedures based on tables containing UDT columns, these objects have to be dropped
- 如果表列引用 UDT，则必须将其转换为基础类型
- 如果表列有默认约束，则删除默认约束
- 如果过程或函数具有 UDT 参数，则必须删除该过程或函数
- 如果 UDT 列上有索引，则必须删除该索引
- 如果索引是主键，则必须删除所有外键
- 如果存在基于 UDT 列的计算列，则必须删除计算列
- 如果这些计算列上有索引，则必须删除这些索引
- 如果存在基于包含 UDT 列的表的模式绑定视图、函数或过程，则必须删除这些对象
回答by phosplait
The simplest way to do this is through Visual Studio's object explorer, which is also supported in the Community edition.
最简单的方法是通过 Visual Studio 的对象资源管理器，社区版也支持它。
Once you have made a connectionto SQL server, browse to the type, right click and select View Code, make your changes to the schema of the user defined type and click update. Visual Studio should show you all of the dependencies for that object and generate scripts to update the type and recompile dependencies.
一旦你已经做了一个连接的类型到SQL Server，浏览，点击右键并选择查看代码，使您更改用户定义类型的模式，然后单击更新。Visual Studio 应向您显示该对象的所有依赖项并生成脚本以更新类型和重新编译依赖项。
回答by mcfea
New answer to an old question:
旧问题的新答案：
Visual Studio Database Projects handle the drop and recreate process when you deploy changes. It will drop stored procs that use UDDTs and then recreate them after dropping and recreating the data type.
部署更改时，Visual Studio 数据库项目会处理删除和重新创建过程。它将删除使用 UDDT 的存储过程，然后在删除和重新创建数据类型后重新创建它们。
回答by Auri Rahimzadeh
I ran into this issue with custom types in stored procedures, and solved it with the script below. I didn't fully understand the scripts above, and I follow the rule of "if you don't know what it does, don't do it".
我在存储过程中使用自定义类型遇到了这个问题，并使用下面的脚本解决了它。我没有完全理解上面的脚本，我遵循“如果你不知道它做什么，就不要去做”的规则。
In a nutshell, I rename the old type, and create a new one with the original type name. Then, I tell SQL Server to refresh its details about each stored procedure using the custom type. You have to do this, as everything is still "compiled" with reference to the old type, even with the rename. In this case, the type I needed to change was "PrizeType". I hope this helps. I'm looking for feedback, too, so I learn :)
简而言之，我重命名旧类型，并使用原始类型名称创建一个新类型。然后，我告诉 SQL Server 使用自定义类型刷新有关每个存储过程的详细信息。您必须这样做，因为即使重命名，所有内容仍然参考旧类型“编译”。在这种情况下，我需要更改的类型是“PrizeType”。我希望这有帮助。我也在寻找反馈，所以我学习:)
Note that you may need to go to Programmability > Types > [Appropriate User Type] and delete the object. I found that DROP TYPE doesn't appear to always drop the type even after using the statement.
请注意，您可能需要转到 Programmability > Types > [Appropriate User Type] 并删除对象。我发现 DROP TYPE 即使在使用语句后似乎也不会总是删除类型。
/* 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;
回答by Ramanjaneyulu
1.Rename the old UDT,
2.Execute query ,
3.Drop the old UDT.
1.重命名旧的UDT，2.
执行查询，3.删除旧的UDT。
回答by Greg
Simple DROP TYPEfirst then CREATE TYPEagain with corrections/alterations?
DROP TYPE先简单，然后CREATE TYPE再进行更正/更改？
There is a simple test to see if it is defined before you drop it ... much like a table, proc or function -- if I wasn't at work I would look what that is?
有一个简单的测试来查看它是否在删除之前已定义......就像一个表、过程或函数——如果我不在工作，我会看看那是什么？
(I only skimmed above too ... if I read it wrong I apologise in advance! ;)
（我也只是略读了一下……如果我读错了，我提前道歉！;）
