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

问题自从 SQL Server 2008 中出现表值参数以来，表类型变得越来越流行。不幸的是，一旦一个表类型被一个或多个对象主动引用，更改就很麻烦。没有 ALTER TYPE，您不能删除和重新创建正在使用的类型。有没有一种简单的方法可以更改所有引用对象？

解决方案最简单的解决方案是创建一个新的表类型，更改引用旧类型的所有对象，然后删除旧类型。让我们从两个存储过程引用的表类型的简单场景开始：

CREATE TYPE dbo.MyType AS TABLE (id int);
GO

CREATE PROCEDURE dbo.MyProcedure1
  @tvp dbo.MyType READONLY
AS
BEGIN
  SELECT id FROM @tvp;
END
GO

CREATE PROCEDURE dbo.MyProcedure2
AS
BEGIN
  DECLARE @tvp dbo.MyType;
END
GO

现在，假设您要更改表类型，以便 id 列支持 bigint 数据类型。如上所述，SQL Server 没有 ALTER TYPE 命令，因此这不起作用：

ALTER TYPE dbo.MyType AS TABLE (id bigint);

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'TYPE'.

您的下一次尝试是删除类型并重新创建它，但这也不起作用：

DROP TYPE dbo.MyType;

Msg 3732, Level 16, State 1, Line 1
Cannot drop type 'dbo.MyType' because it is being referenced by object 'MyProcedure1'. 
There may be other objects that reference this type.

错误消息透露了您的下一个问题：更改 MyProcedure1，然后收到有关 MyProcedure2 的新错误消息。根据您使用这种类型的程度，当您一个一个地发现所有受影响的对象时，这可能是一个漫长而乏味的过程。

因此，为了限制破坏性，让我们创建一个新类型：

CREATE  TYPE dbo.MyOtherType AS  TABLE (id bigint );

我们可以使用以下元数据查询轻松发现引用原始类型的所有对象：

SELECT s.name, o.name, def = OBJECT_DEFINITION (d.referencing_id) 
  FROM sys.sql_expression_dependencies AS d 
  INNER JOIN sys.objects AS o 
     ON d.referencing_id = o.[object_id] 
  INNER JOIN sys.schemas AS s 
     ON o。 [schema_id] = S [schema_id]。
  WHERE d.referenced_database_name IS  NULL 
    AND d.referenced_schema_name = N'dbo ' 
    AND d.referenced_entity_name = N'MyType' ;

这将产生以下结果：

现在您确切地知道要更改哪些对象。更改它们的定义以指向新类型，然后您可以删除旧类型。应用程序代码不必更改，除非您还更改了参数名称：

ALTER PROCEDURE dbo.MyProcedure1
  @tvp dbo.MyOtherType READONLY
AS
BEGIN
  SELECT id FROM @tvp;
END
GO

ALTER PROCEDURE dbo.MyProcedure2
AS
BEGIN
  DECLARE @tvp dbo.MyOtherType;
END
GO

DROP TYPE dbo.MyType;

如果要保留原始类型名称，可以重复该过程：使用新定义重新创建它，然后再次更改所有对象，返回旧类型名称。

原文链接：https://codingdict.com/

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

热门标签