November 13, 2009 at 8:01 am
how can I do an 'in' with a 'like' for example:
select * from sys.syscomments
where text like 'IN' (%pi_max_amt,
amortization_term,
balance_option_code,
date_calc_method1_date,
date_calc_method1_rate,
date_calc_method2_date,
date_calc_method2_rate,
date_calc_method3_date,
date_calc_method3_rate,
date_calc_method4_date,
date_calc_method4_rate,
date_calc_method5_date,
date_calc_method5_rate,
date_selection_method_code)
November 13, 2009 at 8:15 am
Easiest way is going to probably be to just wrap it up in a bunch of OR statements, horrible performance, but from the looks of it you're just trying to find meta data from stored procedures, so you're probably not too concerned about it.
select * from sys.syscomments
where
[text] LIKE '%pi_max_amt'
OR [text] = 'amortization_term'
OR [text] = 'date_calc_method1_date'
OR [text] = 'date_calc_method1_rate'
...
November 13, 2009 at 8:21 am
will that do the 'like' on every column?
November 13, 2009 at 8:37 am
No, my previous comment will only a like against the first predicate.
You'll need to add a LIKE to each OR comparison, from the code you added earlier it appeared you just wanted to do it on the first field. For a like against each predicate it would need to look more like this:
select * from sys.syscomments
where
[text] LIKE '%pi_max_amt%'
OR [text] LIKE '%amortization_term%'
OR [text] LIKE '%date_calc_method1_date%'
OR [text] LIKE '%date_calc_method1_rate%'
November 13, 2009 at 9:06 am
Thought so thanks!!
November 13, 2009 at 10:32 am
Also, if this is sql server 2005 or later, you might consider using the sql_modules tables instead, the old syscomments table broke the code up into multiple rows if the procedure or function was too large, the sql_modules view uses varchar(max) to store the text, and fits it all into one row. Just FYI, I didn't realize this view existed until fairly recently.
November 13, 2009 at 11:45 am
As always, if the inputs are coming from a user interface, you will want to take additional precautions against SQL injection attacks. But either of these approaches should work for you.
declare @likes table (likestring varchar(50) primary key)
insert into @likes
select '%pi_max_amt%' union all
select 'CREATE PROC%' union all
select '%fn_DecodeBitMapFields%' union all
select '%CREATE FNCTION%'
---
select distinct object_id,definition
from sys.sql_modules sm
cross apply (select 1 as hit from @likes where sm.definition like likestring) ca
Or use dynamic SQL.
declare @likes table (likestring varchar(50) primary key)
insert into @likes
select '%pi_max_amt%' union all
select '%amortization_term%' union all
select '%date_calc_method1_date%' union all
select '%CREATE FUNCTION%'
---
declare @sql nvarchar(max)
set @sql = '
select object_id,definition
from sys.sql_modules sm
WHERE
'
select @sql = @sql + ' definition like '+quotename(likestring,'''')+' or '+char(13)
from @likes
select @sql = left(@sql,len(@sql)-4)
print @sql
exec sp_executeSQL @sql
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply