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

How can I alter a user-defined table type in SQL Server ?

如何在SQL Server中更改用户定义的表类型?

7 个解决方案

#1

As of my knowledge it is impossible to alter/modify a table type.You can create the type with a different name and then drop the old type and modify it to the new name

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

Credits to jkrajes

抵免jkrajes

As per msdn, it is like 'The user-defined table type definition cannot be modified after it is created'.

根据msdn，它就像“用户定义的表类型定义在创建后不能修改”。

#2

This is kind of a hack, but does seem to work. Below are the steps and an example of modifying a table type. One note is the sp_refreshsqlmodule will fail if the change you made to the table type is a breaking change to that object, typically a procedure.

这是一种技巧，但似乎确实有用。下面是修改表类型的步骤和示例。如果您对表类型的更改是对该对象的中断更改，通常是一个过程，那么sp_refreshsqlmodule将会失败。

1. Use sp_rename to rename the table type, I typically just add z to the beginning of the name.
2. 使用sp_rename来重命名表类型，我通常只在名称的开头添加z。
3. Create a new table type with the original name and any modification you need to make to the table type.
4. 使用原始名称创建一个新的表类型，并对表类型进行任何修改。
5. Step through each dependency and run sp_refreshsqlmodule on it.
6. 逐步完成每个依赖项并在其上运行sp_refreshsqlmodule。
7. Drop the renamed table type.
8. 删除重命名的表类型。

---

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

WARNING:

警告:

This can be destructive to your database, so you'll want to test this on a development environment first.

这可能会对数据库造成破坏，因此您需要首先在开发环境中对其进行测试。

#3

Here are simple steps that minimize tedium and don't require error-prone semi-automated scripts or pricey tools.

这里有一些简单的步骤，可以减少单调乏味的工作，并且不需要容易出错的半自动脚本或昂贵的工具。

Keep in mind that you can generate DROP/CREATE statements for multiple objects from the Object Explorer Details window (when generated this way, DROP and CREATE scripts are grouped, which makes it easy to insert logic between Drop and Create actions):

请记住，您可以从Object Explorer Details窗口为多个对象生成DROP/CREATE语句(以这种方式生成时，对DROP和CREATE scripts进行分组，这使得在DROP和CREATE actions之间插入逻辑变得很容易):

1. Back up you database in case anything goes wrong!
2. 备份您的数据库，以防任何事情出错!
3. Automatically generate the DROP/CREATE statements for all dependencies (or generate for all "Programmability" objects to eliminate the tedium of finding dependencies).
4. 自动为所有依赖项生成DROP/CREATE语句(或为所有“可编程”对象生成语句，以消除查找依赖项的单调乏味)。
5. Between the DROP and CREATE [dependencies] statements (after all DROP, before all CREATE), insert generated DROP/CREATE [table type] statements, making the changes you need with CREATE TYPE.
6. 在DROP和CREATE [dependencies]语句(毕竟是DROP，在all CREATE之前)之间，插入生成的DROP/CREATE [table type]语句，对CREATE type进行所需的更改。
7. Run the script, which drops all dependencies/UDTTs and then recreates [UDTTs with alterations]/dependencies.
8. 运行脚本，该脚本删除所有依赖项/ udtt，然后重新创建[带有更改的udtt]/依赖项。

---

If you have smaller projects where it might make sense to change the infrastructure architecture, consider eliminating user-defined table types. Entity Framework and similar tools allow you to move most, if not all, of your data logic to your code base where it's easier to maintain.

如果您有较小的项目，在这些项目中更改基础架构可能有意义，请考虑消除用户定义的表类型。实体框架和类似的工具允许您将大部分(如果不是全部的话)数据逻辑移动到易于维护的代码库。

#4

If you can use a Database project in Visual Studio, you can make your changes in the project and use schema compare to synchronize the changes to your database.

如果您可以在Visual Studio中使用数据库项目，您可以在项目中进行更改，并使用模式比较来同步对数据库的更改。

This way, dropping and recreating the dependent objects is handled by the change script.

通过这种方式，删除和重新创建依赖对象将由更改脚本处理。

#5

you cant ALTER/MODIFY your TYPE. You have to drop the existing and re-create it with correct name/datatype or add a new column/s

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

#6

You should drop the old table type and create a new one. However if it has any dependencies (any stored procedures using it) you won't be able to drop it. I've posted another answer on how to automate the process of temporary dropping all stored procedures, modifying the table table and then restoring the stored procedures.

您应该删除旧的表类型并创建一个新的表类型。但是，如果它有任何依赖项(使用它的任何存储过程)，您将无法删除它。关于如何自动删除所有存储过程，修改表，然后恢复存储过程，我已经给出了另一个答案。

#7

Simon Zeinstra has found the solution!

西蒙·泽雷萨找到了解决方案!

But, I used Visual Studio community 2015 and I didn't even have to use schema compare.

但是，我使用了Visual Studio community 2015，我甚至不用使用schema compare。

Using SQL Server Object Explorer, I found my user-defined table type in the DB. I right-mouse clicked on the table-type and selected . This opened a code tab in the IDE with the TSQL code visible and editable. I simply changed the definition (in my case just increased the size of an nvarchar field) and clicked the Update Database button in the top-left of the tab.

使用SQL Server对象浏览器，我在DB中找到了用户定义的表类型。我用鼠标右键单击表格类型并选择。这在IDE中打开了一个代码选项卡，TSQL代码可见且可编辑。我只是更改了定义(在我的例子中，只是增加了nvarchar字段的大小)并单击选项卡左上角的Update Database按钮。

Hey Presto! - a quick check in SSMS and the udtt definition has been modified.

您看!-快速检查SSMS和udtt定义已被修改。

Brilliant - thanks Simon.

聪明的——由于西蒙。