Contents

T-SQL Tuesday 123# Life hacks that make your life easier

It’s that time of the month for another T-SQL Tuesday.

In case you are new to T-SQL Tuesday this is the monthly blog party started by Adam Machanic (b|t) and now hosted by Steve Jones (b|t). It’s a way of encouraging blog posts from the community and helping to share knowledge. This month’s T-SQL Tuesday is hosted by Jess Pomfret (b|t). Jess invites us all to write about your favorite life hack.

My favorite life hack

My first life hack would be PowerShell itself. I use PowerShell throughout the day automating anything repetitive. If your hammer is big enough any idea will be a nail. But that would be too easy right?! Let’s see what we can do with PowerShell profiles.

PowerShell Profiles

Because I use PowerShell regularly, I find myself doing the same thing every time within the console. As a good automator, doing things multiple times gets frustrating.

We can fix that by adding functionality to our PowerShell profile. Your profile is a little script that you can find (or create) which will be loaded every time you start PowerShell.

You can find your profile in your PowerShell directory.

  • PowerShell 5 and lower: “$env:USERPROFILE\Documents\WindowsPowerShell\”
  • PowerShell 6 and above: “$env:USERPROFILE\Documents\PowerShell\”

Profiles for console and VS Code

I have different profiles for the console and VS Code.

The profile for the console is named: Microsoft.PowerShell_profile.ps1 The profile for VS Code is named: Microsoft.VSCode_profile.ps1.

Both the profiles have similar code, but I sometimes need different functionality in the VS Code profile.

The console with automatically find your PowerShell profile when you correctly name it and place it in the right directory. For VS Code, make sure you have enabled “Enable Profile Loading”.

Go to the settings and search for “Profile” to find this setting.

/posts/2020/t-sql-tuesday-123/tsql2sday123_profilevscode.png

What’s in my profile

With PowerShell profiles, you can create little shortcuts to your favorite commands, write functions that do specific things, change your prompt to show specific information, etc etc. My profile contains the following items:

Create PS drives to navigate to specific folders quickly

I spend a lot of time in certain directories, like my repositories. Having a PS drive that points to that location makes things easier

Set aliases for regularly used programs

Oh, aliases make things so much easier. Just type “np” to open Notepad for example.

Change the location to C:\

It’s very annoying when PowerShell decides that your starting directory should be the system directory or your user profile. I want my console to always open in the root of the C-drive.

Change the prompt

How annoying are those very long paths that are shown when we go about 5 levels deep. You barely have any room to type anything before the cursor jumps to the next line. You can change the prompt by using the creating prompt function. In my case, I changed the maximum length of the path to 20 characters.

Show git status in the prompt

Oh, I love git and use it all the time but not seeing the status of my git repository is something that can make things easier.

Fortunately, there is a module called “posh-git” that shows the status of the repo.

We can use that module to display the result in our prompt by using the prompt function again. My prompt looks something like this:

/posts/2020/t-sql-tuesday-123/tsql2sday123_poshgit.png

Re-import certain modules

Doing development on certain modules makes me type the same command, “Import-Module ….” many times during the development process. What if I wrote a little function that would import all the modules I would use in development in one go?

Open websites

Now I’ve just become too lazy. I wanted to open my favorite search websites from powershell when I was dealing with some questions. So I created little functions that would open the DuckDuckGo, Google, or StackOverflow website.

Get loaded assemblies

I’m one of those people that wants to see what is being loaded and in some cases that can help debug certain problems in code. Running that script becomes tedious so I created a little function to get all the assemblies by a certain name.

The profile

My PowerShell profile looks like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
# Create drives
$psDrives = Get-PSDrive

# Create drives
if ($psDrives.Name -notcontains "Repos") {
    $null = New-PSDrive -Name Repos -PSProvider FileSystem -Scope Global -Root "C:\\Users\\sstad\\source\\repos"
}
if ($psDrives.Name -notcontains "Development") {
    $null = New-PSDrive -Name Development -PSProvider FileSystem -Scope Global -Root "C:\\Users\\sstad\\OneDrive\\\_Development"
}
if ($psDrives.Name -notcontains "Presentations") {
    $null = New-PSDrive -Name Presentations -PSProvider FileSystem -Scope Global -Root "C:\\Users\\sstad\\OneDrive\\\_Presentaties"
}

# Get aliases
$aliases = Get-Alias

# Setting Aliases
if ($aliases.name -notcontains "np") {
    $null = New-Item alias:np -value "C:\\Windows\\System32\\notepad.exe"
}


# Make sure the profile starts in the root of the C-drive
Set-Location C:\\

function prompt {
    param(
        \[int\]$Maximum = 20
    )

    $origLastExitCode = $LASTEXITCODE

    $prompt = "$($ExecutionContext.SessionState.Path.CurrentLocation)"

    if ($prompt.length -ge $Maximum) {
        $prompt = "~" + $prompt.Substring($prompt.length - $Maximum + 4)
    }

    $prompt += "$(Write-VcsStatus $status)"

    $prompt += "$(if ($PsDebugContext) {' \[DBG\]:'} else {''})$('>' \* ($nestedPromptLevel + 1)) "

    $LASTEXITCODE = $origLastExitCode
    $prompt
}

function reimport {
    Import-Module Repos:\\PowerShell\\PSFramework\\PSFramework -Force
    Import-Module Repos:\\PowerShell\\dbatools\\dbatools.psm1 -Force
    Import-Module Repos:\\PowerShell\\dbachecks\\dbachecks.psm1 -Force
}

function Duck {
    Start-Process "https://duckduckgo.com/?q=$args"
}

function Google {
    Start-Process "https://www.google.com/search?q=$args"
}

function StackOverflow {
    Start-Process "https://www.stackoverflow.com/search?q=$args"
}

function Get-LoadedAssembly {
    param(
        $name
    )

    if ($name) {
        \[appdomain\]::currentdomain.GetAssemblies() | Where-Object Location -match "$name"
    } else {
        \[appdomain\]::currentdomain.GetAssemblies()
    }
}

# Chocolatey profile
$ChocolateyProfile = "$env:ChocolateyInstall\\helpers\\chocolateyProfile.psm1"
if (Test-Path($ChocolateyProfile)) {
    Import-Module "$ChocolateyProfile"
}

That’s about it

I have a lot more life hacks than just the PowerShell profile, but this is one I don’t notice when I start the console or VS Code, and helps me through the day.

Take advantage of the profile and make your life easier.