February 3, 2016 at 5:04 am
This is a little weird. I have a bit of code which searches for the text of functions and stored procedures. It uses wildcards at the front and end of the search string. Usually it works fine, but yesterday (and today) I got a false positive and I'm not sure why.
The code is
SELECT so.Name, so.type, so.schema_id, su.Name, sm.definition
FROM sys.objects so
INNER JOIN sysusers su
ON so.schema_id = su.UID
INNER JOIN sys.sql_modules sm
ON so.Object_ID = sm.Object_ID
WHERE sm.definition LIKE '%my_vendor_table_name%'--AND so.schema_id <> 1
ORDER BY so.type, so.Name
Our vendor is updating a table name in the database, so I wanted to ensure we changed all the procs that touched that table. When I ran this, I came up with 4 procs, only 3 of which referenced the table name. The fourth proc didn't reference the table name at all.
I've been racking my brain trying to figure out the reason why proc 4 was picked up. Finally, I took at good look at its name which is "uspDB_GetUpdateMyVendorTableName" without any underscores. I'm wondering if it's possible that the underscores in my search are being ignored and if so, how to get around that. I don't want false positives in my code. I want to look for a literal string that might be anywhere in the text of these objects.
Any thoughts or suggestions on this issue?
February 3, 2016 at 5:13 am
Brandie
An underscore is a wildcard for a single character - if you want to find literal underscores then you need to escape them in your search pattern. Having said that, I don't understand why you're getting a result for that fourth stored procedure. If run this, I don't get any results:
IF 'uspDB_GetUpdateMyVendorTableName' LIKE '%My_Vendor_Table_Name%' SELECT 'hello'
John
February 3, 2016 at 5:41 am
John Mitchell-245523 (2/3/2016)
BrandieAn underscore is a wildcard for a single character - if you want to find literal underscores then you need to escape them in your search pattern. Having said that, I don't understand why you're getting a result for that fourth stored procedure. If run this, I don't get any results:
IF 'uspDB_GetUpdateMyVendorTableName' LIKE '%My_Vendor_Table_Name%' SELECT 'hello'John
I forgot about the underscore being a wildcard because I never use it. Thank you for the reminder. But when I tried escaping the underscores in my code, I got no results at all.
So then I tried your little IF statement and got the same results you did. Nothing.
And then I saw something in BOL. Apparently I need to put square brackets around my underscore. So I end up with this and it works. No false positives.
SELECT so.Name, so.type, so.schema_id, su.Name, sm.definition
FROM sys.objects so
INNER JOIN sysusers su
ON so.schema_id = su.UID
INNER JOIN sys.sql_modules sm
ON so.Object_ID = sm.Object_ID
WHERE sm.definition LIKE '%my[_]vendor[_]table[_]name%'--AND so.schema_id <> 1
ORDER BY so.type, so.Name
Thanks for pointing me in the right direction, John.
February 3, 2016 at 5:44 am
Brandie Tarvin (2/3/2016)
But when I tried escaping the underscores in my code, I got no results at all.
WHERE Column LIKE '%/_%' ESCAPE '/'
That would find anything with an _ in the name. If you're looking for 'abc_def', then
WHERE xtd.TextData LIKE '%abc/_def%' ESCAPE '/'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2016 at 5:49 am
GilaMonster (2/3/2016)
Brandie Tarvin (2/3/2016)
But when I tried escaping the underscores in my code, I got no results at all.WHERE Column LIKE '%/_%' ESCAPE '/'
That would find anything with an _ in the name. If you're looking for 'abc_def', then
WHERE xtd.TextData LIKE '%abc/_def%' ESCAPE '/'
NICE! I didn't know ESCAPE could be used as a keyword to define the character. Thanks, Gail. That works too.
February 3, 2016 at 6:33 am
John Mitchell-245523 (2/3/2016)
Having said that, I don't understand why you're getting a result for that fourth stored procedure.
My guess is that the comments in that procedure contain a sentence such as "now I will update my vendor table name". The spaces match the uunderscore wildcard so he gets a match.
I'm always running in the same problem when I do searches on sys.all_sql_modules.
February 3, 2016 at 6:39 am
Hugo Kornelis (2/3/2016)
John Mitchell-245523 (2/3/2016)
Having said that, I don't understand why you're getting a result for that fourth stored procedure.My guess is that the comments in that procedure contain a sentence such as "now I will update my vendor table name". The spaces match the uunderscore wildcard so he gets a match.
I'm always running in the same problem when I do searches on sys.all_sql_modules.
Try either Gail's solution or my John-inspired one in your searches. They make the search more literal and filters out the noise.
February 3, 2016 at 7:12 am
Oh, I know how to fix it, no worries.
But far too often I just start with a copy/paste of the string I am searching for before I realise that there's an underscore in there.
(And I usually can handle the few false positives)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply