SQL Server中修改“用户自定义表类型”问题的分析与方法
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL Server中修改“用户自定义表类型”问题的分析与方法，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3713字，纯文字阅读大概需要6分钟。
内容图文
前言
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做具体的使用
完整的脚本如下
?| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
--判断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。
内容总结
以上是互联网集市为您收集整理的SQL Server中修改“用户自定义表类型”问题的分析与方法全部内容，希望文章能够帮你解决SQL Server中修改“用户自定义表类型”问题的分析与方法所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。