Find text that is not commented 1

[nice95gle]

Would anyone know how to search only for text that is not commented out from the syscomments or information_schema.routines tables.

example 1:

create proc something
as
[COLOR=green]--test[/color]
select somefield as 'anything' from test
go

example 2:

create proc something2
as
[COLOR=green]--test[/color]
select somefield as 'anything' from sometable
go

So if these were the only 2 procs in my db and I queried the above tables for 'TEST'. I would like to return one row, because the only time test shows up the in the something2 procs is in a comment.

Is this possible?

Thanks

Well Done is better than well said
- Ben Franklin

 

[philhege]

It depends on your formatting.

If you allow comments on the same line as a statement, it's a bit tricky.

What about /* */ comments?

 

[philhege]

Here's a simple example:

USE [MyDB] --replace with your database name
DECLARE @searchtext varchar(256)
DECLARE @targetSP sysname
DECLARE @routinebody varchar(8000)
DECLARE @statements table (statement varchar(256))

--SET @targetSP = your routine name
--SET @searchtext = the phrase you want to find

SELECT 
	@routinebody = routine_definition from information_schema.routines
WHERE 
	routine_name = @targetSP

WHILE charindex(char(13),@routinebody) > 0
	BEGIN
		INSERT INTO 
			@statements (statement)
		SELECT 
			replace(ltrim(substring(@routinebody,1,charindex(char(13),@routinebody))),char(9),'')

		SET @routinebody = 
			substring(@routinebody,charindex(char(13),@routinebody)+1,len(@routinebody))
	END
DELETE FROM 
	@statements 
WHERE 
	len(ltrim(statement)) = 0
SELECT * FROM 
	@statements
WHERE 
	charindex(@searchtext,statement) > 0 
AND
	charindex('--',ltrim(statement)) = 0

This assumes the conditions in my earlier post.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[nice95gle]

>> If you allow comments on the same line as a statement, it's a bit tricky.

Maybe there is a way to figire out if it is the first char on a line (just guessing)

>>What about /* */ comments?

I was thinking of playing around with pat or charindex to do that part.

Thanks

Well Done is better than well said
- Ben Franklin

 

[nice95gle]

You are the man . With some tweaking it will do what I want.

Well Done is better than well said
- Ben Franklin

 

[ESquared]

Remember the text is stored in 8000-character chunks so searching each chunk by itself is unreliable.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]