how to identify the objects that are commented in the procedure

  • I am working with SQL Server 2000. I am using the syscomments table to extract the procedure into string. In my project there are some conventions for objects so using string operations I am able to identify the objects used in the procedure. The issue is some of the objects are lying in the commented area of the procedure. Is there a way in which I can identify the code that is commented in the procedure so that I can identify the objects that are lying in the commented area.



  • I need to remove the commented part from the string extracted from syscomments.

  • Lines can be comented by using -- (for a single line of text) or /* */

    The @Script holds the text you retrieved from the syscomments entry and is used here to demonstrate the code

    Here is some code to handle the -- case

    SET @Script = 'DECLARE @S INT /* used to test for comment lines */

    -- Another method to comment line

    SELECT * FROM logentries'



    SET @Pos1 = 0

    SET @Pos2 = 0

    SET @Pos1 = CHARINDEX('--',@Script,1)

    IF @Pos1 > 0


    SET @Pos2 = CHARINDEX(CHAR(10),@Script,@Pos1 + 1) --CHAR(10) = line feed


    --Follwing code is to demonstrate what was retrieved

    SELECT SUBSTRING(@Script,@Pos1,@Pos2 - @Pos1


    -- Another method to comment line

    In a similiar fashion you can use /* to find @Pos1 and for @Pos2 use '*/'

    instead of CHAR(10)

    If everything seems to be going well, you have obviously overlooked something.


    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Thanks Ron...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply