Search Folder Recursively and Report "No matches"

This is a 'continuation' of this question.

Originally, I had asked that, given a list of keywords, and a pointer to a folder, I would like to display all occurrences of where any of the keywords were found, and indicate if no matches were found for that keyword. I have over 300 keywords and about 250 SQL scripts that make up several hundred thousand lines of code, so I am changing the requirements somewhat (although I am impressed how fast PowerShell does this).

The reason for showing lines that included the keywords was so I could check for scenarios where a substring was found (like I_FIS as a keyword found in I_FIS_REJECT), but the output is overwhelming.

Having executed the code suggested in the above link, I am refining the parameters.
First of all, the keywords are table names, and I am checking code to see if the table is referenced. As such, the keyword would almost always be either a full word (such as SELECT * FROM mytable) or be prefixed with a period (.), such as SELECT * FROM dbo.mytable). There should always be whitespace in the form of a space or CR/LF following the keyword, as shown by these examples:
SELECT *
FROM dbo.mytable
WHERE 1 = 1
or
SELECT *
FROM mytable t
WHERE 1 = 1

I want the search and report to be case insensitive, such that, if my keyword is "mytable", "MyTable" and "MYTABLE" count as a match, and I want to report where the keyword is NOT FOUND.

My original request just stated that I would search a folder and subfolders for any file, but I only want to search files with a file type of ".SQL"

The output only has to list the keywords from the list that were not found in any of the scripts.
LVL 15
dbbishopAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

footechCommented:
Try the following.  Once it finds a match in any file it records that keyword, then skips to processing the next keyword, and so on.  At the end the recorded matches are compared against the original list of keywords, and any that are in the original list but not in recorded matches list are output.
$startFolder = "c:\temp"
$patterns = Get-Content "search.txt"
$result = @(foreach ($pattern in $patterns)
{
    $regex = "[\. ]$pattern\b"
    Get-ChildItem $startFolder -filter "*.sql" -Recurse -force | Where { !($_.PsIsContainer) } | ForEach `
    {
        If (Select-String -InputObject $_ -Pattern $regex -Quiet)
        {
            $pattern
            continue
        }
    }
})

Compare-Object $patterns $result -PassThru | ForEach `
{ Write-Output """$_"" was not found" }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbbishopAuthor Commented:
Fantastical!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.