Learn to Work with SharePoint PowerShell Objects and Scripts

SharePoint PowerShell Database Script

Oh, SharePoint PowerShell. In a previous article, we talked about how to get started with PowerShell and at the end I showed you a nasty script. Something like:

Get-SPContentDatabase | %{$db=0} {$db +=$_.disksizerequired; $_.name + " - " + $_.disksizerequired/1mb} {Write-Host "`nTotal Storage (in MB) =" ("{0:n0}" -f ($db/1024/1024)) -backgroundcolor magenta }

That is cumbersome, and if you are new to PowerShell, you aren’t even sure those clicks and bangs are even real.

Let’s just pause for a second and be honest. The whole reason you are here is people post scripts like that on the Internet all of the time and tell you they will cure what ails you. The problem is the Internet is a scary place, and SharePoint PowerShell is a powerful tool. It would be very easy for a bad person to post a script that looks like it will help that actually formats your hard drive or worse yet shaves your dog. (I told you PowerShell was powerful.) You owe it to yourself and your dog to learn how to translate those scripts and make sure they are safe. From there it is a small leap to you writing your own.


Today you are going to learn what that script does, how to translate it, and what all of those crazy pieces are so that you can build your own scripts. Even better is you are going to use the tools you learned last time to break this script down. Enough words? Good, let’s get started.

What Does the Script Do?

This is a handy SharePoint on-prem script. The script finds all of the content databases in your SharePoint farm, reports the size of each database, and then gives you a total. Back in my full-time admin days (20 years ago) I would have had a script like this dump out to a CSV file so that I could track database growth over time. I then used an Excel macro or VBA function to color code cells when they got out of the range I specified. Back then it was for file shares instead of SharePoint, but the concept and the usage are the same today. Making it easier for me to automate my daily routine so that I could play more solitaire. The screenshot at the top of the article shows PowerShell in action.

Breaking the Script Down

The first thing you should do when a script like this arrives in your lap is translate it. To do that you can take advantage of Get-Alias. Remember that all cmdlets are in the form of action-object (verb-noun), so if you don’t see that, then you can use Get-Alias to translate. In this script, the only item to be translated is the % sign. To translate it, in PowerShell type Get-Alias %  and you will find out the cmdlet is ForEach-Object. Cool. We will learn more about that cmdlet a little later. Right now, let’s continue translating the script.

The next unusual thing that you will encounter is { } those squiggly brackets are part of ForEach-Object, so we can safely ignore them right now.


Next up is $db. In PowerShell, a $ represents a variable. Sounds like something we should talk about a little more.

With PowerShell, variables are easy to use. 99 percent of the time you just make up a variable name and assign it a value and your work is done. PowerShell will figure out what type of information you are passing it and set the variable type accordingly. You can make variables that are more complicated, but all you need to understand is set them and forget them. The line $db=0 creates a variable named $db and sets it value to 0. Now we can reuse and edit that variable to our heart’s content. For example, take a look at the screenshot.


Here you see the variable $db being set to 0. Then setting the variable to its current value (0) plus 100. Finally, by typing just $db PowerShell outputs the content of the variable (100) to the screen.

The great news is variables are super easy to use and very useful. The following are a few other basic rules to keep in mind with variables:

  • You don’t have to declare them to use them. So while our script has $db=0 , that is not a requirement. The script does that, so if you run the script twice in a row it will always reset the value to be zero. Keeps you from accidentally getting giant numbers.
  • They can easily be changed. If you use $db=0 , that makes the variable an Int32 object. If in the next line you type $db=”Todd Klindt is a dork.” , then you would be right, and PowerShell would just recreate the variable as a String object. No effort for you.
  • To see what type of variable PowerShell created for you, use $db | Get-Member
  • Variables are temporary. If you close PowerShell, your variables go away.
  • There are other types of variables. Not for today, but if you want to learn A LOT about variables start by typing help about_Variables into PowerShell and read up. It is deep.


This cmdlet is your first chance to learn about looping. The idea is that it will run everything inside of the { } one time for each object that you pass to it. Let’s look at a simple example before we worry about the complex one above.

Get-Process | ForEach-Object{Write-Host $_.name -foregroundcolor red}



As you can see from the screenshot, ForEach-Object runs through everything the { } one time for each object. If you have 100 objects that you pass from the | then the cmdlet will run 100 times. In this case, each time through we had PowerShell write to the host the name of the current process. We did this by using a special variable $_.

$_ is what is called the fill in the blank variable. It represents the current object in your loop. The line $_.name says for the current object give us the value of the name property. This is another advantage of variables — you can directly work with their properties and methods with a dot. Makes for much more compact scripting.

ForEach-Object Another Way

Now that you understand a basic ForEach-Object loop where you have one set of { } brackets now is the time to introduce you to his big brother ForEach-Object with three sets of { }. The way those work are:

ForEach-Object {Do this set first and only once}{Do this set one time for each object}{Do this last and only once}

Read it again. It isn’t that bad. Do something first, usually initialization of some sort. Then do the normal loop through as much as you need. Then output something helpful as you finish up. This is exactly what we are going to demo with our SharePoint script.


The plus symbol has multiple uses. When working with numbers, like in the earlier $db = $db + 100  example, it is for addition. When working with strings, it is used to concatenate (aka add more) additional text. An example of its use is when you want to output both the text that you enclosed in “ “ and text from a variable or function. You could do something like “Today’s date is ” + (get-date)  and the output would be Today’s date is 12/12/2016 15:33:01, as shown in the following screenshot. Lots of string manipulation tricks like this for you to learn as your PowerShell powers grow, but that’s for another day.



Consider this one bonus. This is not PowerShell but a .NET function we can use with PowerShell to format numbers and their decimal places. Very helpful but also a deep rabbit hole. So I will defer you to our friends over at TechNet for details of its inner workings.

Time to Make the Donuts

Here is the script one more time. Now that you know all of the pieces this next part should be easy.

Get-SPContentDatabase | %{$db=0} {$db +=$_.disksizerequired; $_.name + " - " + $_.disksizerequired/1mb} {Write-Host "`nTotal Storage (in MB) =" ("{0:n0}" -f ($db/1024/1024)) -backgroundcolor magenta }

Get-SPContentDatabase will return all of the SharePoint content database objects in your farm. You will need the DBO or SPDataAccess role on the databases for the script to work properly. It then sends all of the database objects over to ForEach-Object thanks to the | (say pipe instead of weird vertical bar).

ForEach-Object is in the three sets of { } squiggly brackets format. The first set contains $db=0 , which creates the variable $db and sets its value to zero. The reason for this is if you run the script a second time you want to start fresh not start with your $db set to the value the first time you ran the script. I learned this the hard way.

The second set of { } will be run once for each object. It starts by taking the value of $db and incrementing it by the value of the current database’s DiskSizeRequired property. The ; (semicolon but you hopefully already knew what that is called) says that line is done but run this next line, also. The second line says $_.name + ” – ” + $_.disksizerequired/1mb . This is going to write to the screen on a new line some text. The text will be the name of the database then a – (dash) and then it is going to take the value of the disksizerequired property and divide it by 1 megabyte. You could use 1gb to divide by gigabytes or if you don’t want to remember that you could divide it by 1024 multiple times. The property is in bytes, so just make sure you convert it to a number you like.

The third set of { } is our last line to output our final tally of $db. Write-Host is used to write output to the screen. I use it a lot, but PowerShell purest don’t like the cmdlet. Ignore them for now and one day when you know all about PowerShell you can also make fun of me for using such a simple cmdlet. The part in “ “ is just plain text to write except for the ‘n. That is a special character that creates a new line.

Now you are left with the part in the ( ) (those are called parenthesis — thanks, Captain Obvious). Just like in math class that means calculate a value and then just output it. In the ( ) we have that .NET function that is going to format the contents of the next set of ( ), which is just our variable $db divided by 1024 twice. In case you are rusty, that is how you convert bytes to megabytes manually. You could easily replace the 1024/1024 with 1mb and get the same results. Finally, there is -backgroundcolor magenta. That just changes the background color of the output and is one of the things that Write-Host does. It is how you win the argument of why you used Write-Host, there is no other way to play with colors.

Wow. That was a lot to cover, but hopefully, it has helped you a couple of different ways. First, you have a script that you can use to calculate your storage space for your content databases without contacting the DBA. Second, you now understand how to break down one of those Internet PowerShell scripts and make sure your dog doesn’t get shaved. And third, you learned some tricks, such as the .NET function, that will make your life easier going forward.

The good news is that in keeping up with my grand tradition of rewarding you for reading, I will now also share with you a link to a video that covers the same concepts with a different script. Manipulating objects in PowerShell should help you to reinforce the concepts above and let you validate you are moving down the road to being a PowerShell guru.