SQL Server中修改“用户自定义表类型”问题的分析与方法

前言

sql server开发过程中，为了传入数据集类型的变量（比如接受c#中的datatable类型变量），需要定义“用户自定义表类型”，通过“用户自定义表类型”可以接收二维数据集作为参数，在需要修改“用户自定义表类型”的时候，增加字段，删除字段，修改字段类型等，它没有像表一样的alter table语法来进行修改。

只能通过删除重建来实现，但是在删除“用户自定义表类型”的时候会提示有对象引用它（某些存储过程用到了这个“用户自定义表类型”），因此无法删除。

为了达到公用的目的，有时候一个tabletype可以在多个地方分别被引用到，这样的话，势必要先删除所有的引用了这个“用户自定义表类型”的对象（存储过程等）

如果这个“用户自定义表类型”被多个存储过程引用，那么就要分别删除多个引用了“用户自定义表类型”的存储过程，然后修改“用户自定义表类型”，在重建存储过程，这样做起来似乎有点绕，这个问题可以用过exec sys.sp_refreshsqlmodule这个系统函数来简介实现“用户自定义表类型”的定义

tabletype的基本使用

如下创建一个用户自定义表类型

定义的tabletype可以在用户自定义表类型中找到

创建两个存储过程，分别用到了上面定义的用户自定义表类型，模拟用户自定义表类型被引用的情况

此时的存储过程可以接收tabletype参数并正常运行

tabletype的修改

tabletype类型不支持alter语法，也即无法直接修改tabletype的定义

那么只能通过删除tabletype的方法来重建这个tabletype，当删除的时候，仍然报错，提示“因为它正由对象 '***' 引用。可能还有其他对象在引用此类型。”

此时只能删除引用了这个tabletype的对象来解决，下面可以查到那些对象引用了某一个tabletype，然后分别删除，重建tabletype，再重建存储过程，有点绕弯子。

可以先将自定义的某个tabletype重命名，重命名的过程中有一个警告，这里先忽略它，随后可以直接drop type dbo.mytabletype

删除原tabletype之后，重建（重定义）tabletype

重建tabletype之后，先前存储过程中用到这个tabletype的存储过程是无法编译通过的

此时就需要重新刷新引用对象的定义

刷新完成之后，原存储过程就可以正常编译了

最后删除原始的tabletype被重命名的tabletype(被第一步重名的那个)

这样子，整个过程就无需因为修改tabletype的定义而删除引用了tabletype的对象了，在修改了tabletype的定义之后，引用了这个tabletype的对象可以正常运行，也可以根据修改之后的tabletype做具体的使用

完整的脚本如下

--判断type是否存在，如果存在，重命名，随后之后才再删除，否则无法直接删除
if exists (select 1 from sys.types t join sys.schemas s on t.schema_id=s.schema_id 
      and t.name='mytabletype' and s.name='dbo')
 exec sys.sp_rename 'dbo.mytabletype', 'obsoleting_mytabletype';
go


--重建type，比如原来是四个字段，现在想修改为三个字段，或者原来有三个字段想加一个字段变成四个字段
create type dbo.mytabletype as table(
 id int not null,
 name varchar(255) not null，　　 remark varchar(255)
)
go

--将原来引用将要删除的type全部重建一遍，否则原始存储过程会报错
declare @name nvarchar(500);
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

--最后删除原始的被重命名的tabletype(被第一步重名的那个)
if exists (select 1 from sys.types t 
   join sys.schemas s on t.schema_id=s.schema_id 
   and t.name='obsoleting_mytabletype' and s.name='dbo')
 drop type dbo.obsoleting_mytabletype
go

--最后执行授权
grant execute on type::dbo.mytabletype to public
go

总结：

tabletype可以方便地接受二维数据作为参数，从而可以达到批量处理数据的目的，避免传递进去一大堆字符串，然后在对字符串解析的做法，从而可以在一定程度上提高sql的运行效率。

不过tabletype的修改确实存在一定的问题，直接修改tabletype会存在级联删除数据库对象的情况，可以通过“曲线救国”的方式，来减小工作量的情况下修改tabletype。

好了，以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对的支持。