0

I’m trying to search the database for any stored procedures that contain one of about 3500 different values.

I created a table to store the values in. I’m running the query below. The problem is, just testing it with a SELECT TOP 100 is taking 3+ mins to run (I have 3500+ values). I know it’s happening due to the query using LIKE.

I’m wondering if anyone has an idea on how I could optimize the search. The only results I need are the names of every value being searched for (pulled directly from the table I created: “SearchTerms”) and then a column that displays a 1 if it exists, 0 if it doesn’t.

Here’s the query I’m running:

SELECT
trm.Pattern,
(CASE 
WHEN sm.object_id IS NULL THEN 0 
ELSE 1
END) AS “Exists”

FROM dbo.SearchTerms trm
LEFT OUTER JOIN sys.sql_modules sm
ON sm.definition LIKE '%' + trm.Pattern + '%'

ORDER BY trm.Pattern

Note: it’s a one-time deal —it’s not something that will be run consistently.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Querty3434
  • 1
  • The optimal way would be to create a CLR table valued function that accepts a string and uses regex to search it and return a result for each of the 3,500 terms matched. Then you only need 1 scan of `sys.sql_modules` and `cross apply` of your function - with each definition being processed once (rather than 3,500+ full scans) – Martin Smith Aug 05 '21 at 07:44

1 Answers1

0

Try CTE and get your Patterns which exists in any stored procedure with WHERE condition using EXISTS (...). Then use LEFT JOIN with dbo.SearchTerms and your CTE to get 1 or 0 value for Exists column.

;WITH ExistsSearchTerms AS (
    SELECT Pattern
    FROM dbo.SearchTerms
        WHERE EXISTS (SELECT 1 FROM sys.sql_modules sm WHERE sm.definition LIKE '%' + Pattern + '%')
)
SELECT trm.Pattern, IIF(trmExist.Pattern IS NULL, 0, 1) AS "Exists"
FROM dbo.SearchTerms trm
    LEFT JOIN dbo.SearchTerms trmExist
        ON trm.Pattern = trmExist.Pattern
ORDER BY Pattern

Reference :

Karan
  • 12,059
  • 3
  • 24
  • 40
  • This was much more efficient. Quick question: how can I tweak the query to display the object name? – Querty3434 Aug 05 '21 at 19:53
  • Query is efficient because when it will short circuit after encountering `first sp` that contains given `Pattern` and `won't check for remaining sp`. If you need only one of the name of `object id` that contains `Pattern` then it can still be efficient but if you want all `Object ids` then your query is the solution. – Karan Aug 06 '21 at 04:09