列名 | 数据类型 | 说明 | ||
---|---|---|---|---|
id | int | 该文本适用的对象 ID。 | ||
number | smallint | 如果进行分组,则为过程分组内的号码。 0 = 项不是过程,1=存储过程。 | ||
colid | smallint | 超过 4,000 个字符的对象定义的行序列号。 | ||
status | smallint | 标识为仅供参考。不提供支持。不保证以后的兼容性。 | ||
ctext | varbinary(8000) | SQL 定义语句的原始字节。 | ||
texttype | smallint | 0 = 用户提供的注释 1 = 系统提供的注释 4 = 加密的注释 | ||
language | smallint | 标识为仅供参考。不提供支持。不保证以后的兼容性。 | ||
encrypted | bit | 指示过程定义是否已经过模糊处理。 0 = 未经模糊处理 1 = 已经模糊处理
| ||
compressed | bit | 总是返回 0。 这表明过程已压缩。 | ||
text | nvarchar(4000) | SQL 定义语句的实际文本。 解码后的表达式的语义等同于原始文本,但不保证语法正确无误。 例如,解码后的表达式中删除了空格。 此 SQL Server 2000 兼容视图从当前 SQL Server 结构中获取信息,可能会返回比 nvarchar(4000) 定义更多的字符。 sp_help 返回 nvarchar(4000) 作为文本列的数据类型。 在使用 syscomments 时,请考虑使用 nvarchar(max)。 对于新的开发工作,请不要使用 syscomments。 |
1、用SysComments的原因
包含数据库中的每个视图、规则、默认值、触发器、CHECK 约束、DEFAULT 约束和存储过程的条目。 text 列包含原始的 SQL 定义语句。
最近新模块的开发,需要更改和新增的存储过程比较多,为了同步开发环境和测试环境的存储过程,能在更新程序后,马上能整理出更改的存储过程脚本,并更新到测试DB服务器上,我用了SqlServer的系统表SysComments,它记录了数据库中所有的对象,当然包括了存储过程,该表有个text字段,它记录了sql定义的脚本内容,如果是存储过程,则记录的存储过程脚本。用它,可以写批量sql语句,直接帮助同步存储过程到测试数据库,因为为了和以后生成环境更新方式一致,需要整理出脚本,因此采用了sql语句获取存储过程内容的方式。
2、遇到问题
在获取存储过程脚本中,遇到了问题,就是更改了存储过程名字后,通过SysComments的text字段获取到的存储过程内容,存储过程名称还是更改前的名称,比如以前存储过程名称叫abc,更改为def,但查询SysComments的text字段内容,同样为create procedure abc。。。这样当每次更新4,50个存储过程,甚至更多的时候,测试系统会发生莫名其妙的问题,因为没有真正更改到需要修改或新增的存储过程。比如,我们先按照最后修改日期查询出最近更改的存储过程:
SELECT TOP 30 a.[name], a.crdate, a.refdate, b.[text]
from sysobjects a, syscomments b
where a.id = b.id and
a.xtype = 'p'
ORDER BY a.refdate DESC
或者用下面的系统视图查询:
SELECT TOP 10 ir.SPECIFIC_NAME, ir.CREATED, ir.LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES ir
ORDER BY ir.LAST_ALTERED DESC
然后查询某一条存储过程内容:
sp_helptext UP_KERNAL_USR_SP_GetListByConsumerID
按常理,这里获取到的脚本,就可以作为该存储过程的更新脚本,但如果存储过程是更改了名称,而没有更改内容,则这个脚本的存储过程名称有可能就不正确了。经过测试,如果修改了存储过程内容,SysComments表中的text字段才会更新为正确的内容,猜测是内容有触发机制,没有更改内容就不触发同步数据到系统表存储,所以只更改名称,SysComments表中的text字段内容没有更新。
3、相关资料:
经过查询,发现微软官网的帮助和支持里有说明:PRB: Renaming Stored Procedure, View or Trigger does not update SYSCOMMENTS table
,具体链接:http://support.microsoft.com/kb/243198/en-us,大概说了确实有这个问题,文章并且说了该问题存在于SQL2005各个版本及以下部分版本,解决方法是删除老存储过程,再创建新存储过程,当然对于不从SysComments的text字段取内容的情况下,直接改名字没什么影响。但该文章是2003年11月14日的,对现在是否有补丁或处理方式,不得而知。
4、总结
我本地Microsoft SQL Server 2005 - 9.00.3042.00版本的SqlServer依然有这个问题,临时解决办法,只有获取内容后,再修改存储过程名称的方式。以后如果用sql脚本读取SysComments的text字段来获取存储过程,需要注意到存储过程名称是否有问题。当然非脚本的手工操作,可以保证不会出现问题,选中数据库-右键-任务-导出脚本。
其他非官方网站也很少搜索到相关内容,如描述有问题,请指出,谢谢!
个人总结:用syscomments时,特别需要注意的是,你若只改了存储过程名字时需要注意就是,但是万物皆在于一个巧字,巧写代码,巧寻问题。