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

这篇文章主要给大家介绍了关于SQL Server中修改“用户自定义表类型”问题的分析与方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧。

前言

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

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

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

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

TableType的基本使用

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

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

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

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

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

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

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

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

TableType的修改

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

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

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

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

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

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

可以先将自定义的某个TableType重命名,重命名的过程中有一个警告,这里先忽略它,随后可以直接Drop Type dbo.MyTableType

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

删除原TableType之后,重建(重定义)TableType

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

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

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

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

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

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

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

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

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

这样子,整个过程就无需因为修改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。

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

原文链接:http://www.cnblogs.com/wy123/p/7282682.html

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/25085/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年9月23日 下午12:20
下一篇 2022年9月23日 下午12:20
软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!