sql-server - How to exclude commented line/text in finding procedure with Text in SQL Server?

Question

I have two procedures as shown below.

Create proc FirstProcdure
As
begin
    select * from MyTableA
    --select * from MyTableB    
end

and

Create proc SecondProcdure
As
begin
    select * from MyTableB
    --select * from MyTableA    
end

Now I want to search procedure with text - MyTableA. It should only come 'FirstProcdure' in result because in 'SecondProcdure' the text MyTableA is commented.

I have tried using below query

Select * from sysobjects where OBJECT_DEFINITION (id) like '%MyTableA%'

Currently it is giving both procedure name in search result.

Sometime we need to change procedure definition quickly & then it is difficult to open find and replace several procedures. So I need to search only procedure name for which the searched text is not the part of commented lines.

标签： sql-server

Answer 1

You can use the custom function for ignore comment text search.

CREATE FUNCTION SearchCommentText(@Value VARCHAR(MAX),@SearchText VARCHAR(MAX))  
RETURNS INT   
AS   
BEGIN  
   DECLARE @cursor INT = 0,@ret INT=0
   declare @commenttext varchar(max)=''
    WHILE PATINDEX('%--%', @Value) > 0 OR    PATINDEX('%/*%', @Value) > 0
    BEGIN
        IF CHARINDEX('--', @Value, 0)BETWEEN 1 AND CHARINDEX('/*', @Value, 0)
            OR    CHARINDEX('/*', @Value, 0) = 0
        BEGIN

            SET @commenttext=@commenttext+ SUBSTRING( @Value,CHARINDEX('--', @Value), ISNULL(NULLIF(CHARINDEX(CHAR(13) + CHAR(10), @Value, CHARINDEX('--',@Value)), 0), LEN(@Value)) - CHARINDEX('--', @Value) + 2)
            SET @Value = STUFF(@Value, CHARINDEX('--', @Value), ISNULL(NULLIF(CHARINDEX(CHAR(13) + CHAR(10), @Value, CHARINDEX('--', @Value)), 0), LEN(@Value)) - CHARINDEX('--', @Value) + 2, '') ;

        END ;
        ELSE
        BEGIN
            SET @cursor = -1 ;
            WHILE CHARINDEX('/*', @Value, @cursor + 1)BETWEEN 1 AND CHARINDEX('*/', @Value, @cursor + 1)
            SET @cursor = CHARINDEX('/*', @Value, @cursor + 1) ;

            set @commenttext=@commenttext+SUBSTRING(@Value, @cursor, CHARINDEX('*/', @Value, @cursor) - @cursor + 2);
            SET @Value = STUFF(@Value, @cursor, CHARINDEX('*/', @Value, @cursor) - @cursor + 2, '') ;
        END ;
    END ;
    IF(@commenttext LIKE '%'+@SearchText+'%' AND @Value NOT LIKE  '%'+@SearchText+'%')
    SET @ret=1;

    RETURN @ret;  
END; 

Execute the following query.

SELECT DISTINCT OBJECT_NAME(sc.id)  FROM syscomments sc
WHERE TEXT like '%MyTableA%' AND dbo.SearchCommentText(sc.text,'MyTableA') <>1