CODEWITHSUNDEEP
sql-server
Suraj Kumar
Suraj Kumar

Reputation: 5643

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

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.

Upvotes: 3

Views: 2988

Answers (2)

Mano
Mano

Reputation: 788

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

Upvotes: 6

Daniel Brughera
Daniel Brughera

Reputation: 1651

What you are looking for a dataobject used in other dataobject is a dependency, so, is too much easier to do it directly

SELECT OBJECT_NAME(id) FROM sys.sysdepends WHERE depid = OBJECT_ID('MyTableA')

If you want to search for a string or another thing that is not dataobject related (For example, depends will not detect if you call your table using dynamic SQL). I prefer to use sp_helptext instead of OBJECT_DEFINITION, if you don't have objections using a loop 

DECLARE @SearchFor VARCHAR(100) = 'MyTableA'
DECLARE @Objects TABLE(name varchar(100))
DECLARE @Result TABLE(name varchar(100))
DECLARE @Lines TABLE(id int identity, line varchar(maX))

INSERT @Objects
SELECT name FROM sys.objects WHERE Type in ('FN', 'IF', 'V', 'P', 'TR', 'TF') 

DECLARE @ObjectName VARCHAR(100)
WHILE EXISTS (SELECT 1 FROM @Objects)
BEGIN
    SELECT TOP 1 @ObjectName = name FROM @Objects

    DELETE @Lines

    INSERT @Lines (line)
    exec sp_helptext @ObjectName

    --Wipe out multiline comments
    DECLARE @Linestart INT, @indexStart INT, @LineEnd INT, @indexEnd INT
    WHILE EXISTS(SELECT 1 FROM @Lines WHERE charindex('/*', line) > 0)
    BEGIN
        SELECT TOP 1 @Linestart = id, @indexStart = charindex('/*', line)
        FROM @Lines WHERE charindex('/*', line) > 0
        ORDER BY id

        SELECT TOP 1 @LineEnd = id, @indexEnd = charindex('*/', line)
        FROM @Lines WHERE charindex('*/', line) > 0
        ORDER BY id

        IF @Linestart = @LineEnd
            UPDATE @Lines SET line = SUBSTRING(line, 1, @indexStart-1) + SUBSTRING(line, @indexEnd+2, LEN(line))
            WHERE id = @Linestart
        ELSE
        BEGIN
            UPDATE @Lines SET line = SUBSTRING(line, 1, @indexStart-1)
            WHERE id = @Linestart
            UPDATE @Lines SET line = SUBSTRING(line, @indexEnd+2, LEN(line))
            WHERE id = @LineEnd
            DELETE @Lines WHERE id > @Linestart AND id < @LineEnd
        END
    END

    IF EXISTS (
        SELECT 1 
        FROM @Lines
        OUTER APPLY (
            SELECT charindex(@SearchFor, line) A, charindex ('--', line) B
        ) S
        WHERE A > 0 AND (B = 0 OR A < B) )
    BEGIN
        INSERT @Result VALUES (@ObjectName)
    END


    DELETE @Objects WHERE name = @ObjectName
END
SELECT * FROM @Result

Upvotes: 2

Related Questions