MS-SQL] sp_helptxt 만들기...

 

 

sp_helptext 를 하면 애써 맞춰놓은 정렬이 다 깨져 나와서

 

그리드가 아닌 text로 바꿔 실행해서 본다거나 해당 프로시져를 찾아서 수정 눌러서 본다거나...

 

귀찮아진다.

 

기존껀 긴건 짤린다 해서 = varchar( max )

 

sp_helptext를 수정하였음.

 

아래 파란색 글씨만 추가해서 새로 등록함.

 

 1.  스크립트 생성 [ SP_HELPTXT ]

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[SP_HELPTXT]    Script Date: 11/22/2012 11:55:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_HELPTXT] 
 @objname nvarchar(776) 
   ,@columnname sysname = NULL 
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 
 
/* NOTE: Length of @SyscomText is 4000 to replace the length of 
** text column in syscomments. 
** lengths on @Line, #CommentText Text column and 
** value for @DefinedLength are all 255. These need to all have 
** the same values. 255 was selected in order for the max length 
** display using down level clients 
*/ 
,@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 
 
/* 
**  See if @objname exists. 
*/ 
select @objid = object_id(@objname) 
if (@objid is null) 
        begin 
  raiserror(15009,-1,-1,@objname,@dbname) 
  return (1) 
        end 
 
-- If second parameter was given. 
if ( @columnname is not null) 
    begin 
        -- Check if it is a table 
        if (select count(*) from sys.objects where object_id = @objid and type in ('S ','U ','TF'))=0 
            begin 
                raiserror(15218,-1,-1,@objname) 
                return(1) 
            end 
        -- check if it is a correct column name 
        if ((select 'count'=count(*) from sys.columns where name = @columnname and object_id = @objid) =0) 
            begin 
                raiserror(15645,-1,-1,@columnname) 
                return(1) 
            end 
    if (ColumnProperty(@objid, @columnname, 'IsComputed') = 0) 
  begin 
   raiserror(15646,-1,-1,@columnname) 
   return(1) 
  end 
 
        declare ms_crs_syscom  CURSOR LOCAL 
        FOR select text from syscomments where id = @objid and encrypted = 0 and number = 
                        (select column_id from sys.columns where name = @columnname and object_id = @objid) 
                        order by number,colid 
        FOR READ ONLY 
 
    end 
else if @objid < 0 -- Handle system-objects 
 begin 
  -- Check count of rows with text data 
  if (select count(*) from master.sys.syscomments where id = @objid and text is not null) = 0 
   begin 
    raiserror(15197,-1,-1,@objname) 
    return (1) 
   end 
    
  declare ms_crs_syscom CURSOR LOCAL FOR select text from master.sys.syscomments where id = @objid 
   ORDER BY number, colid FOR READ ONLY 
 end 
else 
    begin 
        /* 
        **  Find out how many lines of text are coming back, 
        **  and return if there are none. 
        */ 
        if (select count(*) from syscomments c, 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 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 syscomments where id = @objid and encrypted = 0 
    ORDER BY number, colid 
  FOR READ ONLY 
 
    end 
 
/* 
**  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 
 
IF @Line is NOT NULL 
    INSERT #CommentText VALUES( @LineId, @Line ) 

declare @lineNo int = 0;
declare @lineCnt int = 0;
declare @printtext varchar(max);
select @lineCnt = COUNT( LineId ) from #CommentText;
while( @lineNo < @lineCnt )
begin
 set @lineNo = @lineNo + 1;
 
 select @printtext = Text from #CommentText
 where LineId = @lineNo ;
 
 print( @printtext );
end
CLOSE  ms_crs_syscom 
DEALLOCATE  ms_crs_syscom 
 
DROP TABLE  #CommentText 
 
return (0) -- sp_helptext 

 

 

 

 

2. 프로시져를 시스템프로시져로 만든다.

USE [master]
GO
exec sp_ms_marksystemobject 'sp_helptxt'

 

3. 단축키에 등록해서 사용하면

 

print 되기때문에 탭유지되면서 잘 나온다 복사 해서 쓰면 됨.