如何在 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' ;

这将产生以下结果:

type.png

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

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/