sqlserver怎么修改表结构(SQL Server中修改“用户自定义表类型”问题的分析与方法)
sqlserver怎么修改表结构
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。
好了,以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对开心学习网的支持。
- SQLServer中防止并发插入重复数据的方法详解(SQLServer中防止并发插入重复数据的方法详解)
- sql2008提供身份认证模式(SqlServer2000+ 身份证合法校验函数的示例代码)
- sqlserver纵表变横表(SQL Server行转列的方法解析)
- SqlServer类似正则表达式的字符处理问题(SqlServer类似正则表达式的字符处理问题)
- sql server怎么导出数据库(sqlserver复制数据库的方法步骤图文)
- sqlserver字段说明(详解SQL Server 中 JSON_MODIFY 的使用)
- sqlserver2016使用教程(SQL Server 2016 Alwayson新增功能图文详解)
- SqlServer中如何解决session阻塞问题(SqlServer中如何解决session阻塞问题)
- sqlserver索引介绍(浅析SQL Server的聚焦使用索引和查询执行计划)
- sqlserver基础查询语句(SQL Server子查询的深入理解)
- sqlserver数据库的对象有哪些(详解SQL Server数据库架构和对象、定义数据完整性)
- sqlserver百分比数据查询时间(SQL Server统计信息更新时采样百分比对数据预估准确性的影响详解)
- sqlserver触发器编写(SQLSever中的触发器基本语法与作用)
- sqlserver常见函数(SQL Server之JSON 函数详解)
- sqlserver数据库中锁的4种类型(SQLSERVER对加密的存储过程、视图、触发器进行解密推荐)
- sqlserver语句创建窗口布局(SQL Server 2012 开窗函数)
- 手机QQ与小米路由器在一起 明天揭晓,敬请期待(手机QQ与小米路由器在一起)
- 小米音乐与 QQ 音乐合作,便捷迁移会员(小米音乐与QQ音乐合作)
- 小米推出米兔儿童电话手表奥特曼版,799 元,支持微信 QQ(小米推出米兔儿童电话手表奥特曼版)
- 贾怀胤唱《白龙马》 炸场 了 没想到京剧还能这么玩(贾怀胤唱白龙马)
- 白龙马的改编学生版,快来看看(白龙马的改编学生版)
- 萌娃唱《白龙马》走红,那生动的小表情,网友直呼 简直是戏精(萌娃唱白龙马走红)
热门推荐
- scrollWidth,clientWidth,offsetWidth的区别
- mvc中@helper的用法
- SQL中AVG、COUNT、SUM、MAX等函数对NULL值处理
- 用css实现图片特效代码(HTML+CSS+JavaScript实现图片3D展览的示例代码)
- python3配置教程(python3中property使用方法详解)
- Http协议头信息
- c语言可以实现python所有功能吗(Python实现的调用C语言函数功能简单实例)
- linux系统登录mysql数据库(Linux手动部署远程的mysql数据库的方法详解)
- html5添加背景(Html5实现首页动态视频背景的示例代码)
- 阿里云注册域名ddns怎么写(如何通过阿里云实现动态域名解析DDNS的方法)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9