PowerShell: Get Files Containing a String

I have hundreds of script files, and as hard as I try, I don’t always name them effectively. Many of those scripts contain an obscure piece of code that I’m not very familiar with. I normally refer to previous scripts to remember the rarely used bits. While the standard windows search feature helps me narrow down which file I need when I’ve named the file efficiently, it fails to help a lot. When I’m looking for a script with a particular line of code on say, line 1021, where I did a weird time conversion, I’m not going to find it fast, if at all.

Last week I was hunting for a script that used the PowerShell feature of creating a Progress Bar. In the past I’ve mostly used the bar as a gimmick, but this time I wanted to keep track of how far along a script was in a long-running multi-server query. I quickly scripted something to search the contents of my documents for a specific piece of text. I even added a progress bar to this script. Granted the search is normally so fast that the bar doesn’t matter, but hey, I needed to practice the calculations for the progress bar anyways.


Function Search-Folder
{
  param(
  [Parameter(Mandatory=$true)]
    [string]$Folder,
  [Parameter(Mandatory=$true)]
    [string]$SearchTerm
  )
 $Files = Get-ChildItem $Folder
 $Count = $Files.Count
 $i = 0
 ForEach( $File in $Files )
 {
  $Percent = $i/$Count*100
  Write-Progress -Activity "Searching through files..." -Status "$Percent% Complete" -PercentComplete $Percent
  $i++
  $Text = Get-Content "$Folder\$File" -ErrorAction SilentlyContinue
  IF( $Text | WHERE { $Text -LIKE "*$SearchTerm*" } )
  { Write-Host $File }
 }#EndForEach
}#EndFunction

Most of the math and code here is to display the Progress Bar. The function takes two mandatory parameters, $Folder and $SearchTerm. It then loops through each file in the folder using Get-ChildItem and then Get-Content, filtering for the desired text string.

I commonly clear the screen with CLS before executing a function, just to keep the results easy to read. The below example execution will cycle through my PowerShellScripts folder and return the name of any files with the word progress in them.


Search-Folder -Folder "C:\Scripts\PowerShellScripts" -SearchTerm "progress"

PowerShell: Get SQL Servers From AD

Today I’d like to share a quick script that is useful when working with a new domain of SQL servers. It’s also good for anyone who doesn’t know their environment very well or suspects there might be some hidden or rarely used servers. There are probably many ways to do this scattered around the internet, but this method is easy and as long as you have a properly designed Active Directory, you’ll be good to go.

The script isn’t anything special, but it will require the Active Directory PowerShell module. You’ll need to run the script on an existing terminal server or at least be able to successfully import the module. Importing a module is very straightforward and this one should already be available on a terminal server.

Import-Module ActiveDirectory

Once you have the module loaded, you can run the following script.

Get-ADComputer -Filter { Description -LIKE "*sql*" } -Properties Description | SELECT Name, Description

Let’s break this down since this is a short post. First of all, we are running the Get-ADComputer cmdlet. That’s going to search through your Active Directory for a list of computer names. The -Filter parameter is looking for any Description that contains the word SQL. If you want to instead search for a name prefix, you can change the filter to { Name -LIKE "Prefix*" }. The -Properties Description parameter will populate the description of the computers from AD. Without that piece, your descriptions will be blank even though we are specifically selecting them. Finally, we pipe the results to a SELECT since I’m only interested in the name of the computers and their descriptions.

This script relies on your Active Directory having useful descriptions. If your coworkers have been lazy and left those blank or put in less than useful information…it won’t be very effective.

Fixing Hotkeys in SQL 2012

A colleague was working in SQL Management Studio as usual today when he tried to hide the results pane using Ctrl + R. It didn’t work though. He hit it a few more times. Nothing. Finally, in frustration, he asked if anyone knew what was going on. I had encountered this exact problem awhile back and had to look up a fix, so I rolled over to help. It took a few moments, but after pulling up the menu, I was able to direct him to the proper place to revert the settings. (and not create a custom hotkey like I did when this happened to me originally)

Starting with SQL Server 2012, Microsoft decided to simplify their hotkeys to follow the style of Visual Studio. The problem is, for us SQL folks, we don’t know those hotkeys. More importantly, most of us are stuck in our ways with no desire to change something that works. Just take a look at the new hotkey. It’s ridiculous.

WindowsHideResults

2 keys or 4, your choice

 

Microsoft, in their finite wisdom, realized that some people might not like this change, and kept a simple interface option to revert back to original hot keys. You can find the fix under the Tools menu -> Options.

ToolsOptions

From there, expand Environment, then select Keyboard. At the top of the dialog box you will see a drop down about keyboard mapping scheme. Select Default or hit Reset. Then press OK and you should be good to go.

ToolsOptionsKeyboard

I always get lost in the options, so I blog so I don’t have to remember

 

Now when you press Ctrl + R, it should once again Show/Hide the Results pane. The default hotkey option will change some others as well, probably most notably Execute goes from Ctrl + Shift + E back to F5 and Execution Plan hot keys get simplified. Sadly, if you use multiple computers to run SSMS, you’ll have to make this adjustment on each of them.