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。

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

(0)

相关推荐

  • Sqlserver 表类型和表变量介绍

    表类型可以用在存储过程中,用于批量增加表类型定义: 复制代码 代码如下: CREATE TYPE dbo.SubCardTable as table  (       [SC_ID] [varchar](50),       [ZhuKaInfo_ID] [varchar](50),       [Project_KeyName] [varchar](50),       [SC_CardNumber] [varchar](50),       [Statues] [int] DEFAULT 0

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

    前言 SQL Server开发过程中,为了传入数据集类型的变量(比如接受C#中的DataTable类型变量),需要定义"用户自定义表类型",通过"用户自定义表类型"可以接收二维数据集作为参数,在需要修改"用户自定义表类型"的时候,增加字段,删除字段,修改字段类型等,它没有像表一样的alter table语法来进行修改. 只能通过删除重建来实现,但是在删除"用户自定义表类型"的时候会提示有对象引用它(某些存储过程用到了这个&qu

  • SQL Server中聚合历史备份信息对比数据库增长的方法

    很多时候,在我们规划SQL Server数据库的空间,或向存储方面要空间时,都需要估算所需申请数据库空间的大小,估计未来最简单的办法就是看过去的趋势,这通常也是最合理的方式. 通常来讲,一个运维良好的数据库都需要做定期基线(baseline),有了基线才会知道什么是正常.一个简单的例子例如,一些人的血压平常偏低,那么80的低压对他来说就是不正常了.但现实情况是大多数系统并没有采集基线的习惯,因此在需要规划空间想要看历史增长时,就没有过去精确的数据了. 一个解决办法就是通过查看历史备份的大小来看过

  • SQL Server中统计每个表行数的快速方法

    我们都知道用聚合函数count()可以统计表的行数.如果需要统计数据库每个表各自的行数(DBA可能有这种需求),用count()函数就必须为每个表生成一个动态SQL语句并执行,才能得到结果.以前在互联网上看到有一种很好的解决方法,忘记出处了,写下来分享一下. 该方法利用了sysindexes 系统表提供的rows字段.rows字段记录了索引的数据级的行数.解决方法的代码如下: 复制代码 代码如下: select schema_name(t.schema_id) as [Schema], t.na

  • SQL Server中检查字段的值是否为数字的方法

    最近有一个项目,需要检查某个字符型字段的值是否为数字,由于数据行是海量,所以需要通过SQL语句来筛查,这里拿来做个备忘. 用到的函数为IsNumeric,具体语句如下所示: 复制代码 代码如下: SELECT * FROM 表 WHERE IsNumeric(需要检查的字段名) = 0 ISNUMERIC  确定表达式是否为一个有效的数字类型.       语法    ISNUMERIC   (   expression   )       参数    expression       要计算的

  • SQL SERVER中关于exists 和 in的简单分析

    In与Exists这两个函数是差不多的,但由于优化方案不同,通常NOT Exists要比NOT IN要快,因为NOT EXISTS可以使用结合算法二NOT IN就不行了,而EXISTS则不如IN快,因为这时候IN可能更多的使用结合算法. 如图,现在有两个数据集,左边表示#tempTable1,右边表示#tempTable2.现在有以下问题: 1.求两个集的交集? 2.求tempTable1中不属于集#tempTable2的集? 先创建两张临时表: create table #tempTable1

  • Sql Server中判断表、列不存在则创建的方法

    一.Sql Server中如何判断表中某列是否存在 首先跟大家分享Sql Server中判断表中某列是否存在的两个方法,方法示例如下: 比如说要判断表A中的字段C是否存在两个方法: 第一种方法  IF EXISTS ( SELECT 1 FROM SYSOBJECTS T1 INNER JOIN SYSCOLUMNS T2 ON T1.ID=T2.ID WHERE T1.NAME='A' AND T2.NAME='C' ) PRINT '存在' ELSE PRINT '不存在' 第二种方法,短小

  • 解析SQL Server中datetimeset转换datetime类型问题

    在SQL Server中,数据类型datetimeoffset转换为datetime类型或datetime2类型时需要特别注意,有可能一不小心你可能会碰到下面这种情况.下面我们构造一个简单案例,模拟一下你们可能遇到的情况. CREATE TABLE TEST ( ID INT IDENTITY(1,1) ,CREATE_TIME DATETIME ,CONSTRAINT PK_TEST PRIMARY KEY(ID) ); GO INSERT INTO TEST(CREATE_TIME) SEL

  • 关于SQL Server中bit类型字段增删查改的一些事

    前言 本文主要给大家介绍了关于SQL Server中bit类型字段增删查改的一些事,话说BIT类型字段之前,先看"诡异"的一幕,执行Update成功,但是查询出来的结果依然是1,而不是Update的2 当别人问起我来的时候,本人当时也是处于懵逼状态的,后面联想具体的业务突然想起来这个字段是bit类型的 如果把这个现象跟BIT类型字段连续起来就不觉得奇怪了. 废话不多,直接上代码看结果就好了. 先建一个测试表 CREATE TABLE TestBIT ( Id INT IDENTITY(

  • Sql Server中清空所有数据表中的记录

    Sql Server中清空所有数据表中的记录 清空所有数据表中的记录: 复制代码 代码如下: exec sp_msforeachtable  @Command1 ='truncate table ?' 删除所有数据表: 复制代码 代码如下: exec sp_msforeachtable 'delete   N''?''' 清空SQL Server数据库中所有表数据的方法(有约束的情况) 其实删除数据库中数据的方法并不复杂,为什么我还要多此一举呢,一是我这里介绍的是删除数据库的所有数据,因为数据之

  • SQL Server中的约束(constraints)详解

    目录 一.约束的分类 二.约束命名 三.主键约束 1.在创建表的时候创建主键约束. 2.在已存在的表上创建主键约束 3.复合主键的创建 四.外键约束 4.1.创建表的时候创建外键 4.2.在已存在的表中添加一个外键 4.3.级联动作 五.唯一约束 主键和唯一约束的区别: 六.CHECK约束 七.DEFAULT约束 7.1在创建表时定义DEFAULT约束: 7.2在已存在的表上添加DEFAULT约束: 八.禁用约束 8.1.在创建约束时,忽略检查之前的不满足数据 8.2.临时禁用已存在的约束 九.

随机推荐