使用sh_helptext可以查询存储过程或视图的文本格式，对它改动后还有更好的用法......

使用sh_helptext可以查询存储过程或视图的文本格式，对它改动后还有更好的用法...

系统存储过程：sp_helptext +存储过程名称或视图名 用法也就是对存储过程或视图的内容文本显示。在这里将它修后可以保存到数据表中，方便我们导出.txt或其他用途。

下面看代码，其实也很简单..：

/* ================================================
将查询的文本，插入数据表中，很有意思玩一玩
首先创建表： create table aa_temp01(id int identity(1,1),objname varchar(7999),value Nvarchar(4000))
然后执行：exec [sp1_helptext_aa2] 'sp1_helptext_aa2'
查看结果：select * from aa_temp01
drop table aa_temp01
========================================================== */

alter procedure [ dbo ] . [ sp1_helptext_aa2 ]
@objname nvarchar ( 776 ) -- 说明:参数只能为存储过程名称或视图名
as
set nocount on
declare @dbname sysname
, @objid int
, @BlankSpaceAdded int
, @BasePos int
, @CurrentPos int
, @TextLength int
, @LineId int
, @AddOnLen int
, @LFCR int -- lengths of line feed carriage return
, @DefinedLength int
, @SyscomText nvarchar ( 4000 )
, @Line nvarchar ( 255 )

select @DefinedLength = 255
select @BlankSpaceAdded = 0 /* Keeps track of blank spaces at end of lines. Note Len function ignores
trailing blank spaces */
CREATE TABLE #CommentText
(LineId int
, Text nvarchar ( 255 ) collate database_default)
/*
** Make sure the @objname is local to the current database.
*/
select @dbname = parsename ( @objname , 3 )
if @dbname is null
select @dbname = db_name ()
else if @dbname <> db_name ()
begin
raiserror ( 15250 , - 1 , - 1 )
return ( 1 )
end
-- print 'dbname:'+@dbname
-- -select @objid = object_id(@objname)
select @objid = OBJECT_ID from sys.objects where name = @objname
-- print @objid
if ( @objid is null )
begin
raiserror ( 15009 , - 1 , - 1 , @objname , @dbname )
return ( 1 )
end
begin
/*
** Find out how many lines of text are coming back,
** and return if there are none.
*/
if ( select count ( * ) from sys.syscomments c, sys.sysobjects o where o.xtype not in ( ' S ' , ' U ' )
and o.id = c.id and o.id = @objid ) = 0
begin
raiserror ( 15197 , - 1 , - 1 , @objname )
return ( 1 )
end

if ( select count ( * ) from sys.syscomments where id = @objid and encrypted = 0 ) = 0
begin
raiserror ( 15471 , - 1 , - 1 , @objname )
return ( 0 )
end

declare ms_crs_syscom CURSOR LOCAL
FOR select text from sys.syscomments where id = @objid and encrypted = 0
ORDER BY number , colid
FOR READ ONLY
end
-- print 'objid:'+convert(varchar,@objid)
/*
** else get the text.
*/
select @LFCR = 2
select @LineId = 1

OPEN ms_crs_syscom
FETCH NEXT from ms_crs_syscom into @SyscomText

WHILE @@fetch_status >= 0
begin
select @BasePos = 1
select @CurrentPos = 1
select @TextLength = LEN ( @SyscomText )

WHILE @CurrentPos != 0
begin
-- Looking for end of line followed by carriage return
select @CurrentPos = CHARINDEX ( char ( 13 ) + char ( 10 ), @SyscomText , @BasePos )

-- If carriage return found
IF @CurrentPos != 0
begin
/* If new value for @Lines length will be > then the
**set length then insert current contents of @line
**and proceed.
*/
while ( isnull ( LEN ( @Line ), 0 ) + @BlankSpaceAdded + @CurrentPos - @BasePos + @LFCR ) > @DefinedLength
begin
select @AddOnLen = @DefinedLength - ( isnull ( LEN ( @Line ), 0 ) + @BlankSpaceAdded )
INSERT #CommentText VALUES
( @LineId ,
isnull ( @Line , N '' ) + isnull ( SUBSTRING ( @SyscomText , @BasePos , @AddOnLen ), N '' ))
select @Line = NULL , @LineId = @LineId + 1 ,
@BasePos = @BasePos + @AddOnLen , @BlankSpaceAdded = 0
end
select @Line = isnull ( @Line , N '' ) + isnull ( SUBSTRING ( @SyscomText , @BasePos , @CurrentPos - @BasePos + @LFCR ), N '' )
select @BasePos = @CurrentPos + 2
INSERT #CommentText VALUES ( @LineId , @Line )
select @LineId = @LineId + 1
select @Line = NULL
end
else
-- else carriage return not found
begin
IF @BasePos <= @TextLength
begin
/* If new value for @Lines length will be > then the
**defined length
*/
while ( isnull ( LEN ( @Line ), 0 ) + @BlankSpaceAdded + @TextLength - @BasePos + 1 ) > @DefinedLength
begin
select @AddOnLen = @DefinedLength - ( isnull ( LEN ( @Line ), 0 ) + @BlankSpaceAdded )
INSERT #CommentText VALUES
( @LineId ,
isnull ( @Line , N '' ) + isnull ( SUBSTRING ( @SyscomText , @BasePos , @AddOnLen ), N '' ))
select @Line = NULL , @LineId = @LineId + 1 ,
@BasePos = @BasePos + @AddOnLen , @BlankSpaceAdded = 0
end
select @Line = isnull ( @Line , N '' ) + isnull ( SUBSTRING ( @SyscomText , @BasePos , @TextLength - @BasePos + 1 ), N '' )
if LEN ( @Line ) < @DefinedLength and charindex ( ' ' , @SyscomText , @TextLength + 1 ) > 0
begin
select @Line = @Line + ' ' , @BlankSpaceAdded = 1
end
end
end
end

FETCH NEXT from ms_crs_syscom into @SyscomText
end
-- print 'Line:'+@Line
IF @Line is NOT NULL
INSERT #CommentText VALUES ( @LineId , @Line )

-- -将查询到的文本写入到这个表里
insert into aa_temp01
select @objname , Text from #CommentText order by LineId

CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom

DROP TABLE #CommentText
return ( 0 ) -- sp_helptext

下面附上结果图：

把存储过程或视图，插入到数据表中，能使用的场合就更多了......

posted on 2011-02-18 14:34 一路->向前 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/howzanh/archive/2011/02/18/1957812.html