SQL Server中修改“用户自定义表类型”问题的分析与方法
作者:MSSQL123 发布时间:2024-01-24 01:13:58
前言
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。
好了,以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。
来源:http://www.cnblogs.com/wy123/p/7282682.html
猜你喜欢
- 阅读目录源码请参考:starnightcyber/scripts主要使用TelnetClient类,封装了登录和执行命令的基本操作。源码如下
- 1.Fork出来的Git仓库同步代码背景:有的时候从原仓库fork出了一个新仓库,这个新仓库做了自己的修改。可是原仓库也进行了更新,比如修复
- 目录什么是信号Python的信号处理Python对信号的处理信号枚举信号函数signal.alarm(time)signal.pause()
- GetRef 函数 返回一个指向一过程的引用,此过程可绑定某事件。 Set object.eventname = GetRef(procna
- 前言:最近写爬虫会经常遇到一些验证码识别的问题,现如今的验证码已经是五花八门,刚开始的验证码就是简单的对生成的验证码图片进行一些干扰,但是随
- 前言这部分已经折腾我两天了,还是没有头绪,可能还会折腾更久,最后在第三天上午解决问题,在一个不起眼的地方被坑了,jQuery加载的问题。会者
- 在使用python编程过程中,我们往往需要借助字典来提高编程效率。同时为了调试方便,我们希望将某些变量保存为中间文件。例如,在协同过滤算法中
- 如果需要一个简单的Web Server,而不是安装那些复杂的HTTP服务程序,比如:Apache,Nginx等。那么可以使用Python自带
- 本文实例讲述了PHP基于非递归算法实现先序、中序及后序遍历二叉树操作。分享给大家供大家参考,具体如下:概述:二叉树遍历原理如下:针对上图所示
- 关于Python的文件遍历,大概有两种方法,一种是较为便利的os.walk(),还有一种是利用os.listdir()递归遍历。方法一:利用
- 1、控制"纵打"、 横打”和“页面的边距。 (1)<script defer> function SetPr
- 总所周知bilibili是没有办法直接查看弹幕的发送者的
- 这篇文章主要介绍了python3 tcp的粘包现象和解决办法解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值
- 除了硬盘和磁带,现在您可以使用 SQL Server 的本机备份功能来备份您的 SQL Server Database到 Windows A
- 内置数据类型文本类型:str数值类型: int,float,complex序列类型:list,tuple,range映射类型: dict集合
- 前言cookie:在网站中,http请求是无状态的。也就是说即使第一次和服务器连接后并且登录成功后,第二次请求服务器依然不能知道当前请求是哪
- JetBrainsMono 是 JetBrains 公司开发的一款开源字体,可免费商用。正如其名字带的Mono,即Monospaced Fo
- 这篇文章主要介绍了Python属性和内建属性实例解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友
- Oracle存储过程基本语法 存储过程 1 CREATE OR REPLACE PROCEDURE 存储过程名 2 IS 3 BEGIN 4
- 用了两种方法保存图片,opencv和Image,实践证明opencv非常快from PIL import Imageimport osimp