改进版的sp_helptext

  SQL Server中系统自带的sp_helptext存在一些问题, 如显示格式不易阅读,空格未自动过滤等,

现开发改进型sp_helptext2, 可直接显示为text格式,且能自动找出同义词的源对象.

if exists(select 1 from sys.objects where type='P' and name='sp_helptext2')
   drop proc dbo.sp_helptext2
go 

create proc dbo.sp_helptext2
(@objectname sysname)
as
begin
set nocount on
declare @objectid int,
              @objecttype varchar(10),
              @Print nvarchar(max)

select @objectid=object_id,
           @objecttype=type
 from sys.objects 
 where type in('P','V','TR','FN','SN')
 and name=@objectname

if @objectid is null
begin
  print 'Invalid object name '''+@objectname+'''. '
  return
end

if @objecttype='SN'
begin
  select @Print='Synonym: '+@objectname+char(13)+char(10)
                          +'BaseObject: '+base_object_name 
    from sys.synonyms 
    where name=@objectname
  print @Print
  return
end

declare @T table(Col nvarchar(max))

insert @T(Col)
  select object_definition(@objectid)+char(13)+char(10)

while(select Col from @T)<>''
begin  
   select @Print=replace(left(Col,charindex(char(13)+char(10),Col)-1),char(32),' ')
     from @T

   print rtrim(@Print)

   update @T set Col=stuff(Col,1,charindex(char(13)+char(10),Col)+1,'')  
end

end