相信,大家对sp_helptext存储过程一定不陌生,它可以帮你快速获取存储过程等对象的定义。但它有一个致命的缺点就是:每行最多返回255个nvarchar类型的字符,假如有一个编写不规范的存储过程,它的某些行超出了255,则取出的定义内容,很可能出错。其实,对象的定义都已经完整地存储 在它所在数据库的 sys.sql_modules 视图中(兼容性视图为:sys. syscomments ),下面介绍一个小方法来解决这个问题:
USE tempdbGO--创建测试存储过程CREATE PROC dbo. Demo1 @Length INTASSET NOCOUNT ONIF(@Length > 0 ) PRINT '>0'ELSE PRINT '<=0'GO--获取存储过程定义DECLARE @ObjectName NVARCHAR( 50)SET @ObjectName = N'Demo1'SELECT ObjectDefinition =b. name FROM(SELECT Content=CONVERT (XML, '')) aOUTER APPLY(SELECT [name] = C. v.value ('.', 'NVARCHAR(MAX)') FROM a.Content .nodes( '/root/v') C(v )) b--直接拷贝结果/*--创建测试存储过程CREATE PROC dbo.Demo1 @Length INTASSET NOCOUNT ONIF(@Length > 0) PRINT '>0'ELSE PRINT '<=0'*/
小结
#1. 突破了每行255个字符的限制。每行字符长度无限制,输出总内容长度无限制。
#2. 可以直接从表格结果集中粘贴出结果,而不用再切换到文本显示选项(Ctrl+T)或用PRINT函数打印到输出窗口(PRINT函数输出的最大长度为8000)
#3. 果断定义成快捷键,放到SQL Prompt的Snippet Manager中
#4. 看了下sp_helptext的定义了解到:#1.系统对象的object_id为负整数,用户对象的object_id为正整数 #2.syscomments视图text列类型为nvarchar(4000),也就是说,假设一个存储过程的内容长度为4001个字符,那么它将分2行存储在syscomments中 #3.sp_helptext输出结果以换行+回车(char(13)+char(10))为拆分输出行,如果输出行的长度大于255,则按每行最大255个字符再次拆分。