How can I find code with a specific string, but not in comments

Question

I'm trying to search through 1,000's of stored procedures and functions for a particular string (in this case an email address), but I want to ignore it when it's in a comment block.

This is the SQL syntax to find the objects, but there are hundreds of results and I don't want to go through each one to determine if the email address is being utilized in code or just in a comment block.

SELECT DISTINCT
  o.name objectname
FROM sys.sql_modules m
  INNER JOIN sys.objects o 
    ON m.object_id = o.object_id
WHERE definition LIKE '%[email protected]%'
ORDER BY o.name

I have a CLR library (SQLSharp) where I can use regex, but I'm not sure of the regex expression that would identify a comment vs valid code.

For those who are RegEx savvy and not SQL Server savvy, a comment is defined as a single line starting with two dashes (e.g. --) or multiple lines starting with /* and ending with */

Just the thought of trying to figure this out makes my brain hurt.

Answer 1

This can be done using the code I posted in an article on SQL Server Central:

Searching Objects for Text

The Stored Procedure described in that article has the ability to not only ignore both inline (--) and block (/* ... */) comments, but it can also ignore text within single quotes. The code defaults the option to ignore comments to True as it seems unlikely to want to search for non-impacting text. But the default for ignoring strings is False as dynamic SQL and some other text that can go into a string might still be valid, executable code in the end.

The code was developed for SQL Server 2000 and hence does not make use of NVARCHAR(MAX) in 1 or 2 places that would really help. I have since updated the code to use NVARCHAR(MAX) instead of NVARCHAR(4000) and hence a single temp table, but have not updated the article with those changes. I will try to do that one of these days. And I believe the code in the article looks at one database whereas the updated code will look through all accessible DBs if one is not specified.