Technical Article

A Simple way to find the line of script.

,

You can use this procedure to find any word or sentance, which u used in procedures, views, triggers and function.
This procedure is basically a modified form of system procedure "sp_helptext". Now its upto you to use that and modified that. To execute the procedure, first create it in your Database and then execute it by giving any character (which u used in some script)
this procedure will return you the name of Object, line # and the original text.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE  PROCEDURE SP_Admin_SearchTxt @Txt varchar(255)--,@typ Varchar(20) NULL
As
set nocount on

CREATE TABLE #temp
(
id int identity(1,1),
name varchar(255)
)

CREATE TABLE #CommentText
(
LineId INT,
name VARCHAR(255),
text  VARCHAR(255)
)

INSERT INTO #temp (name)
SELECT name from sysobjects where xtype in ('TR','V','P','Fn','Tf')


DECLARE
@objname VARCHAR(255),
@counter INT
SET @counter = 0

WHILE @counter <= (SELECT ISNULL(max(id),0) FROM #Temp)
BEGIN
Set @objname = NULL
SELECT @objname = name FROM #Temp WHERE id = @Counter
DECLARE 
@dbname varchar(100),
@BlankSpaceAdded INT,
@BasePos       INT,
@CurrentPos    INT,
@TextLength    INT,
@LineId        INT,
@AddOnLen      INT,
@LFCR          INT, --lengths of line feed carriage return,
@DefinedLength INT,
@SyscomText VARCHAR(4000),
@Line          VARCHAR(255)
  
  
SELECT 
@DefinedLength = 255 ,
@BlankSpaceAdded = 0 



SELECT @dbname = PARSENAME(@objname,3)
    DECLARE ms_crs_syscom  CURSOR LOCAL
    FOR
SELECT
text
FROM SYSCOMMENTS
WHERE
id = OBJECT_ID(@objname)
AND encrypted = 0
    ORDER BY
number,
colid
    FOR READ ONLY  
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  
            While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength  
            BEGIN  
                SELECT @AddOnLen = @DefinedLength - (ISNULL(LEN(@Line),0) + @BlankSpaceAdded)  
                INSERT INTO #CommentText
(
LineId,
name,
text
) 
VALUES
                (
@LineId,
@objname,
LTRIM(RTRIM(isnull(@Line,'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), '')))
)  
                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 INTO #CommentText(LineId,name,text) VALUES( @LineId,@objname,LTRIM(RTRIM(@Line)) )  
            SELECT @LineId = @LineId + 1  
            SELECT @Line = NULL  
        END  
        ELSE  

        BEGIN  
            IF @BasePos <= @TextLength  
            BEGIN  
                While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength  
                BEGIN  
                    SELECT @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0)  + @BlankSpaceAdded )  
                    INSERT INTO #CommentText(LineId,name,text) VALUES  
                    ( @LineId,  @objname,LTRIM(RTRIM(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 charindex(' ', @SyscomText, @TextLength+1 ) > 0  
                BEGIN  
                    SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1  
                END  
                BREAK  
            END  
        END  
    END  
  
 FETCH NEXT FROM ms_crs_syscom into @SyscomText  
END  
  
IF @Line is NOT NULL  
INSERT INTO #CommentText(LineId,name,text) VALUES( @LineId,@objname, LTRIM(RTRIM(@Line)) )  
CLOSE  ms_crs_syscom  
DEALLOCATE  ms_crs_syscom  
SET @Counter = @Counter + 1
END  

SELECT
a.name,
(case xtype
when 'v' then 'view'
when 'p' then 'procedure'
When 'tr' then 'trigger'
END) As object_type,
a.lineid,
a.text
from #CommentText a
JOIN sysobjects o on a.name = o.name
WHERE a.text LIKE @Txt

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating