Post reply

search in text of a procedure with skiping comments

  • August 13, 2004 at 4:22 am

    #181485

    Hi,

    I have a script to search a word (some text in general) in all stored procedures text, something like :

    select object_name(id) from syscomments where text like %MyWord%

    I'd like to skip comments in my script, so MyWord is not returned if it is included in a comment: .../* ... MyWord ...*/ ...

    Any idea?

  • August 13, 2004 at 7:34 am

    #518856

    You'd have to have some looping mechanism to find the places where /* and */ are, position I mean and then substring them out.

    So

    create procedure myproc as /* test */ select myfield from mytable return.

    /* is at position 28 and */ is at 37 (end char), so

    declare @cmd varchar(100)

    select @cmd = 'create procedure myproc as /* test */ select myfield from mytable return'

    select substring( @cmd, 1, 27) + ' ' + substring( @cmd, 38, 200)

    returns the proc minusthe comments. If you had multiple comments, you'd need to loop through until charindex() returned nothing.

    select charindex( '*/', 'create procedure myproc as /* test */ select myfield from mytable return')

  • August 13, 2004 at 7:48 am

    #518869

    I've already begun applying this, but for now only for comments like "-- some comment".

    It's not verry easy cause a procedure may have more than 1 row in syscomments, and in this case a comment can begin by example at the end of the 2nd row in syscomments and ends in 3rd row.

    So, what I'm doing now exactly is to search for "--", and Char(13) + Char(10) after that, and I use stuff to remove the text between.

    Thanks for responding, any suggestion is welcome.

  • August 13, 2004 at 7:59 am

    #518876

    I solved the problem of comments begining on one row and ending on the next row. But I still have a beautifull  error:

    "-" is the last character of a row and the first character of the next row

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

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