Is this query possible? 1

[djj55]

Hello, SQL2008R2

I am trying to find stored procedures that have 'EXEC' or 'EXECUTE'. Now I would like to exclude the procedures where the only place EXEC exists is commented out. I am using the column [definition] from table sys.sql_modules to look at the code.
I thought about something like

declare @temp1 TABLE (testtext varchar(max));
insert into @temp1 VALUES 
('This is a test EXEC abc'),
('This is a test EXEC abc -- EXEC'),
('This is a test EXEC abc  EXEC cde'),
('This is a test -- EXEC'),
('This is a test EXEC cde');
-- return all five
select * from @temp1;

-- returns only one - but I would like three
select * from @temp1
where 1=1
    and testtext like '%EXEC%'
    and testtext not like '%-- EXEC%'
    and testtext NOT LIKE '%EXEC cde%';

I would like to get the first three records but not the last two. This may not be possible.
Thank you,



djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[bborissov]

select * from @temp1
where (testtext like '%EXEC%'
    and testtext not like '%-- EXEC%'
    and testtext NOT LIKE '%EXEC cde%')
    OR testtext like '%EXEC abc%'

Borislav Borissov
VFP9 SP2, SQL Server

 

[djj55]

Thank you Borislav, the problem is I do not know the 'abc' value what I expect is most will have ’(@str)‘ or a variation.
It could also be 'EXECUTE (@str)'.

By using your method and I can get it to work using exclude.

select * from @temp1
where (testtext like '%EXEC%'
    and testtext not like '%-- EXEC%'
    and testtext NOT LIKE '%EXEC cde%')
    OR testtext like '%EXEC [^cde]%'

Thank you again

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Do you need to deal with comments like this?

/*
  Exec (@str)

*/

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fredericofonseca]

I would replace like '%-- EXEC%' with like '%--%' + 'EXEC%'

And neither of the options above will identify cases where the comment out code is on a /* ... */ block

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[djj55]

The /*..*/ comment is not a problem.
This is not a critical procedure, just an exercise that I am doing.
I really appreciate all the assistance. As it has gotten a much shorter list than I had before.
Thanks again,


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

I think the trick here is to remove all comments first. After that, searching for the string should be easy. To that end, I have created a user defined function that will strip out all comments. This means you will need to create the user defined function first.

Create Function dbo.StripComments(@HTML VarChar(max))
Returns VarChar(Max)
As
Begin
  Declare @StripExtra Int,
          @StartTag VarChar(50), 
          @EndTag VarChar(50)

  Select  @StartTag = '/*',
          @EndTag = '*/',
          @HTML = LTrim(RTrim(@Html))

  Set @StripExtra = Len(@StartTag + @EndTag) -2

  While @HTML Like '%' + @StartTag + '%'
    Set @HTML = Stuff(@HTML, PatIndex('%' + @StartTag + '%', @HTML), CharIndex(@EndTag, @HTML, PatIndex('%' + @StartTag + '%', @HTML))-PatIndex('%' + @StartTag + '%', @HTML)+@StripExtra, '')

  Select @StartTag = '--',
         @EndTag = Char(13) + Char(10)

  Set @StripExtra = Len(@StartTag + @EndTag) -2

  While @HTML Like '%' + @StartTag + '%'
    Set @HTML = Stuff(@HTML, PatIndex('%' + @StartTag + '%', @HTML), CharIndex(@EndTag, @HTML, PatIndex('%' + @StartTag + '%', @HTML))-PatIndex('%' + @StartTag + '%', @HTML)+@StripExtra, '')


  Return LTrim(RTrim(@HTML))
End

I will admit that this code is rather ugly, and somewhat complicated. However, it is relatively fast, too.

Once you have the function created, you can use it like this...

Select	object_name(object_id)
From	sys.sql_modules
Where	definition like '%exec%'
        And dbo.StripComments(definition) Like '%exec%'

When I ran this code on my database, it searched through 3,173 code blocks in approximately 1 second. I would not use this function in production code because functions can be somewhat slow, but for searching through code, it should work just fine.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thanks George. It takes about a minute on our development server.
I had been using SQLCop and thought about the cases where we call code.


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!