I got caught out by a Gotcha which has caught me out many times and hopefully wont again ( but it will). I was looking for a specific piece of text within a sql module in a Database, simple right! So in this case I was looking for a cdc text identifier this was in the format of SCHEMA_TABLENAME.

For illustration purposes I’m going to use dbo_myDataTable. I was trying to look for any stored procedure that had this. so I used my go to query of

SELECT * 
FROM sys.sql_modules
WHERE definition like '%dbo_myDataTable%'

Like is a great method of filtering data. I’d expected only one maybe two records returned. However I got loads of results. So I started looking for the text fragment. The first record did not have this text anywhere with in the definition column. I thought this is strange and moved to the second, again no references.

It was about this time I kicked myself, I’ve been caught out again.

The underscore character is a wildcard search flag. This checks if there is 1 and only 1 character of any character. This is different from the Percent(%) symbol, which stands for 0 to many characters of any character.

So my search was pulling any record dbo<any char>myDataTable within the definition column. this meant anything for example dbo.myDataTable would be returned. I didn’t want these records.

So more importantly how do you fix this?

If you use square brackets it is possible to search for any single character within the brackets. I.e. Value Like a[bcd_]e can return abe, ace, ade or a_e but not aee. This also allows you to use the specific underscore symbol, as an underscore character. So I reran my above query with a little tweaking.

SELECT * 
FROM sys.sql_modules
WHERE definition like '%dbo[_]myDataTable%'

This returned the one record I was looking for.