Hall of Fame
Group: General Forum Members
Last Login: 2 days ago @ 1:58 PM
The code uses LIKE to compare the string, so just construct your string accordingly.
I'll add code to see the actual part(s) of the text that match if you want.</snip>
I don't even know where to begin for that...
Here's an example; naturally change the "backup" bytes and the total bytes listed to whatever you prefer:
IF OBJECT_ID('tempdb..#search_strings') IS NOT NULL
DROP TABLE #search_strings
CREATE TABLE #search_strings (
search_id int IDENTITY(1, 1) NOT NULL,
search_string nvarchar(40) NOT NULL
INSERT INTO #search_strings
--UNION ALL SELECT 'linked_server_name2'
--UNION ALL ...
OBJECT_NAME(object_id) AS Object_Name,
--list 100 bytes of the definition, starting 40 bytes before the string itself was found
(SELECT SUBSTRING(definition, derived.string_location - 40, LEN(search_string) + 100) FROM sys.sql_modules WHERE object_id = derived.object_id) AS Object_Text
PATINDEX('%' + ss.search_string + '%', sm.definition) AS string_location
FROM sys.sql_modules sm
CROSS JOIN #search_strings AS ss
CAST(OBJECTPROPERTYEX(sm.object_id, 'IsProcedure') AS int) = 1 AND
sm.definition LIKE '%' + ss.search_string + '%'
) AS derived
SQL DBA,SQL Server MVP('07, '08, '09)
"If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them." James Blackburn, in closing argument in the "Fatal Vision" murders trial
Group: General Forum Members
Last Login: Today @ 2:55 PM
Sorry for necro'ing an old thread, but I'm trying to do something similar. The difference is that I'm looking for multiple strings (matching a pattern) of varying lengths. I've tried to modify the "dog" query, but am falling short of accomplishing what I want to accomplish. Can anyone help shed light?
FYI, I'm trying to search through stored procs and other objects for instances of linked servers, which follow a standard of "LS-%".
select object_name(s.id) Object, substring(s.text, n, charindex(' ', text, n) - n) as Target
from syscomments s
where t.n < (select max(len(text)) from syscomments)
and lower(substring(s.text, case when t.N > len(s.text) then len(s.text) else t.n end, case when t.N > len(s.text) then 5 else len(s.text) - n end)) like 'ls-__'
order by object, target
Heh... I guess I'm necro'ing a necro'd thread but let me ask... are you all set on this?
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013
"Has anyone ever told you that a query you have written runs too fast?" - Dwain Camps - 6 Mar 2014
How to post code problems
How to post performance problems