在SQL Server中修改用户定义的表类型

Question

我如何在SQL Server中修改用户定义的表类型？

- yogi

---

可能是如何更改表值参数的重复问题。 - Aaron Bertrand

可能是如何更改表值参数的重复问题。 - KyleMit

Answer 1

据我所知，修改表类型是不可能的。您可以使用不同的名称创建该类型，然后删除旧类型并将其修改为新名称。

感谢jkrajes。

根据msdn，就像“创建后无法修改用户定义的表类型定义”。

- Manivannan Nagarajan

---

我使用一系列生成的下拉/创建脚本来创建所有对象，这些对象取决于用户定义的类型和用户定义的类型本身。 - Ronnie Overby

如何在SQL中得到20,000行以上的脚本？删除/创建您需要更改的表类型的所有依赖项。：P - Spacemonkey

这太糟糕了（微软），现在每当我更改这些表格时，都必须修改所有存储过程，然后删除，重新创建并更新任何对它们的引用。这个设计思路太差了... 我知道这是一次抱怨，但他们当时在想什么呢？ - schmoopy

似乎您甚至不能更改用户定义表类型的所有者。如果需要修改所有者名称，则删除表类型并使用新的所有者名称再次创建似乎是唯一的方法。 sp_changeobjectowner不能在用户定义的表类型上工作... - Jeff

Answer 2

这种方法有点取巧，但似乎可行。以下是修改表类型的步骤和示例。需要注意的是，如果您对表类型所做的更改是破坏该对象（通常是过程）的更改，则sp_refreshsqlmodule将失败。

1. 使用sp_rename重命名表类型，我通常只在名称前面添加z。
2. 创建一个新的表类型，并进行任何必要的修改。
3. 逐个检查每个依赖项并运行sp_refreshsqlmodule。
4. 删除已重命名的表类型。

---

EXEC sys.sp_rename 'dbo.MyTableType', 'zMyTableType';
GO
CREATE TYPE dbo.MyTableType AS TABLE(
    Id INT NOT NULL,
    Name VARCHAR(255) NOT NULL
);
GO
DECLARE @Name NVARCHAR(776);

DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + '.' + referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.MyTableType', 'TYPE');

OPEN REF_CURSOR;

FETCH NEXT FROM REF_CURSOR INTO @Name;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sys.sp_refreshsqlmodule @name = @Name;
    FETCH NEXT FROM REF_CURSOR INTO @Name;
END;

CLOSE REF_CURSOR;
DEALLOCATE REF_CURSOR;
GO
DROP TYPE dbo.zMyTableType;
GO

警告：

这可能会破坏你的数据库，因此你应该先在开发环境中进行测试。

- Nick O

---

在MSDN链接上，有关sp_refreshsqlmodule的备注似乎具有威胁性。但是这个存储过程是否还更新了引用对象的文本内容，还是只更新它们的编译形式？我的意思是如果我在其中一个被引用的存储过程上运行SP_HELPTEXT命令，那么其中是否会得到新的类型名称？ - yogi

这不会改变您存储过程中的表类型为 z 表类型。它只是刷新现有模式，但修复了对表类型的引用。如果在重新创建表类型后不在存储过程上运行 sp_refreshsqlmodle，则存储过程将失败并显示表类型已更改。我建议在生产环境之前在开发版本的数据库上运行此操作。 - Nick O

这个答案对我没有用。一旦执行sp_rename，SP上的参数类型就会更新为引用新的UDTT。然后在重命名后的UDTT无法删除。sp_rename强制执行依赖项。这个答案假设sp_rename不会更新引用SP的参数类型。 - murtazat

使用 sp_rename 重命名用户定义的数据类型时，会抛出错误：参数 @objname 不明确或声明的 @objtype ((null)) 错误。可能出了什么问题？ - Muflix

@Muflix，你可能需要像这样传入对象类型：EXEC sys.sp_rename 'dbo.MyTableType'，'zMyTableType'，'OBJECT';。此外，请确保第一个参数中包含模式，而第二个参数不包含。 - Nick O

@norlando 当我添加了模式信息后，它可以工作了，谢谢。 - Muflix

Answer 3

以下是简单的步骤，可以最小化乏味的工作，并且不需要容易出错的半自动化脚本或昂贵的工具。请记住，您可以从“对象资源管理器详细信息”窗口生成多个对象的DROP/CREATE语句（以这种方式生成时，DROP和CREATE脚本被分组，这使得在Drop和Create操作之间插入逻辑变得容易）：

1. 在出现问题的情况下备份数据库！
2. 自动生成所有依赖项的DROP/CREATE语句（或为所有“可编程性”对象生成语句，以消除查找依赖项的烦恼）。
3. 在所有DROP之后、所有CREATE之前的[dependencies]语句之间插入生成的DROP/CREATE [table type]语句，并使用CREATE TYPE进行所需更改。
4. 运行脚本，该脚本将删除所有依赖项/UDTT并重新创建[带有修改的UDTT]/依赖项。

---

如果你有较小的项目，并且改变基础架构可能是合理的，考虑消除用户定义的表类型。Entity Framework和类似工具允许您将大多数，如果不是全部，数据逻辑移至代码库中，这样更易于维护。

要为多个对象生成DROP/CREATE语句，请右键单击数据库，选择任务，然后选择生成脚本...（如下图所示）。请注意:

1. DROP语句位于CREATE语句之前。 2. DROP语句按依赖顺序排列（即与CREATE相反）。 3. CREATE语句按依赖顺序排列。

- lightmotive

---

我采纳了你的建议，考虑将数十万行代码迁移到使用Entity Framework，以避免必须删除并重新创建用户定义的表类型。但是，经过再次考虑，我决定暂时采用删除和重新创建表类型的更简单的方法。 - PapillonUK

@PapillonUK，我跟你一样。我有一个更大的项目，所以我仍然在做与你相同的决定（而且上面的方法非常快，我可能会在可预见的未来继续使用它）。我提到了摆脱UDTTs对于那些有较小项目的人来说，现在改变架构可能有助于简化未来的更改，因此我已经相应地更新了我的答案。谢谢。 - lightmotive

Answer 4

Simon Zeinstra找到了解决方案！

不过，我使用的是Visual Studio Community 2015，甚至都没有使用Schema Compare。

通过使用SQL Server Object Explorer，我在数据库中找到了我的用户定义表类型。我右键点击表格类型并选择 "xxxxx"。这打开了一个代码选项卡，在IDE中可见TSQL代码并且可以 编辑 。 我只需更改定义（在我的情况下只是增加了nvarchar字段的大小），然后单击选项卡左上角的Update Database按钮即可。

嘿，太棒了！ - 在SSMS中快速检查，udtt定义已经被修改。

太棒了-谢谢Simon。

- BioEcoSS

---

这可能更适合作为对答案的评论，而不是作为自己的答案。 - Phil Cooper

说实话，我觉得这非常有用，对于像我这样不太熟悉Visual Studio数据库项目的人来说，这是解决问题的简单途径，并且解释了如何做到这一点。 - Steve Kennaird

我也发现这个答案很有帮助。 - Padmika

谢谢分享 =D - Ayorus

Answer 5

如果您可以在Visual Studio中使用数据库项目，您可以在项目中进行更改，并使用模式比较来将更改同步到您的数据库中。 这样，删除和重新创建相关对象是由更改脚本处理的。

- Simon Zeinstra

---

我发现这个答案很有帮助，还有BioEcoSS的答案。 - Padmika

Answer 6

您需要删除旧表类型并创建新表。但是，如果有任何依赖项（任何使用它的存储过程），您将无法删除它。我在这里发布了另一个答案，介绍了如何自动删除所有存储过程、修改表格并恢复存储过程的过程。

- BornToCode

---

难道不是理所当然的吗？否则你为什么要创建它呢？它肯定有依赖关系。 - LarryBud

@LarryBud - 一个简单的例子可能是如果有人刚刚创建了它，还没有机会在任何存储过程等中使用它。 - BornToCode

Answer 7

你不能修改已有的数据类型，必须删除现有的类型并使用正确的名称/数据类型重新创建它，或者添加一个新的列。

- user2767892

Answer 8

我在项目中不得不对用户定义的表类型进行更改，以下是我采用的步骤：

1. 查找使用用户定义表类型的所有存储过程。
2. 保存所有找到的存储过程的创建脚本。
3. 删除这些存储过程。
4. 保存要更改的用户定义表的创建脚本。 4.5 向用户定义表类型中添加所需的额外列或更改。
5. 删除用户定义表类型。
6. 运行用户定义表类型的创建脚本。
7. 运行存储过程的创建脚本。
8. 然后开始相应地修改存储过程。

- J G

Answer 9

我为此创建了两个存储过程。第一个名为create_or_alter_udt_preprocess，它以UDT名称作为输入，删除使用该UDT的所有存储过程/函数，删除UDT，并返回一个SQL脚本来重新创建所有的过程/函数。

第二个存储过程名为create_or_alter_udt_postprocess，它接收第一个过程输出的脚本并执行它。

有了这两个过程，更改UDT可以通过以下步骤完成：

1. 调用create_or_alter_udt_preprocess； 2. 使用新定义创建UDT； 3. 调用create_or_alter_udt_postprocess；

请使用事务以避免在出现错误时丢失原始过程。

create or ALTER   proc create_or_alter_udt_postprocess(@udt_postprocess_data xml)
as 
begin
    if @udt_postprocess_data is null 
        return;

    declare @obj_cursor cursor 
    set @obj_cursor = cursor fast_forward for 
    select n.c.value('.', 'nvarchar(max)') as definition
    from @udt_postprocess_data.nodes('/Objects/definition') as n(c)

    open @obj_cursor;

    declare @definition nvarchar(max);
    fetch next from @obj_cursor into @definition;
    while (@@fetch_status = 0)
    begin
        exec sp_executesql @stmt= @definition
        fetch next from @obj_cursor into @definition
    end

    CLOSE @obj_cursor;
    DEALLOCATE @obj_cursor; 
end

Create or ALTER   proc create_or_alter_udt_preprocess(@udt nvarchar(200), @udt_postprocess_data xml out) 
AS
    BEGIN
        set @udt_postprocess_data = null;
        if TYPE_ID(@udt) is null
            return;

        declare @drop_scripts nvarchar(max);
        SELECT @drop_scripts =  (
        (select N';'+ drop_script
            from 
        (
        SELECT 
            drop_script = N'drop ' + case sys.objects.type when 'P' then N'proc ' else N'function' end
                + sys.objects.name + N';' + + nchar(10) + nchar(13)
        FROM sys.sql_expression_dependencies d
        JOIN sys.sql_modules m ON m.object_id = d.referencing_id
        JOIN sys.objects ON sys.objects.object_id = m.object_id
        WHERE referenced_id = TYPE_ID(@udt)
        ) dependencies
        FOR XML PATH (''), type
        ).value('.', 'nvarchar(max)')
         ) ;
    
        declare @postprocess_data xml;

        set @udt_postprocess_data =
        (SELECT 
            definition
        FROM sys.sql_expression_dependencies d
        JOIN sys.sql_modules m ON m.object_id = d.referencing_id
        JOIN sys.objects ON sys.objects.object_id = m.object_id
        WHERE referenced_id = TYPE_ID(@udt)
        FOR XML PATH (''), root('Objects'));
        
        exec sp_executesql @stmt= @drop_scripts;
        exec sp_droptype @udt;
  END

使用示例：

begin tran
declare @udt_postprocess_data xml;

exec create_or_alter_udt_preprocess @udt= 'test_list', @udt_postprocess_data = @udt_postprocess_data out;
CREATE TYPE test_list AS TABLE(
    test_name nvarchar(50) NULL
);

exec create_or_alter_udt_postprocess @udt_postprocess_data = @udt_postprocess_data;

commit;

设置示例使用的代码：

CREATE TABLE [dbo].[test_table](
    [test_id] [int] IDENTITY(1,1) NOT NULL, [test_name] [varchar](20) NULL
) ON [USERDATA]
GO


CREATE TYPE test_list AS TABLE(test_name nvarchar(20) NULL)
GO

create proc add_tests(
@test_list test_list readonly)
as 
begin
    SET NOCOUNT ON;
    insert into test_table(test_name)
    select test_name
    from @test_list;
end;

create proc add_tests2(
@test_list test_list readonly)
as 
begin
    SET NOCOUNT ON;
    insert into test_table(test_name)
    select test_name
    from @test_list;
end;

- SeanLi