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

Share

Share

Rate