• Nate Schmidt - Thursday, April 13, 2017 6:33 AM

    Question: could this be combined somehow with Redgate's SQL Search tool.  Or (alternately) with a stored procedure that searches for a specified text string.  Goal here is to remove or ignore the 'false positives' that show up when I'm doing an impact analysis for an impending database upgrade.  References to said database show up in the comments or documentation section of a sproc, and I'd prefer (perhaps optionally) that those non-programmatic references be ignore.

    That's a really good question. I can't speak to whether or not this can be incorporated into the Redgate tools. But searching database objects is the entire reason that I created this script in the first place. While Redgate's tools are awesome, the search tool isn't great when you need to run massive searches for many objects or many strings. So, when I have the function created, I can then write something like the code below. It's still possible to have false positives in something like a literal string, but this gets me 99% accurate results when searching for a certain table for example. 


    SELECT o.name, o.xtype    
    FROM    sys.sql_modules m            
    INNER JOIN sys.sysobjects o                
        ON m.object_id = o.id            
    WHERE    o.xtype IN ('P', 'V', 'TR', 'TF', 'FN', 'IF')    
        --AND m.definition LIKE '%Something%'    
        AND dbo.RemoveComments(m.definition) like '%something%'