Powershell Function to Get Sizes of All Databases on an Instance

Below is a function that I modified from one in Idera’s SQL Server Powershell Scripts toolset:

[sourcecode language=”powershell”]
#Function to get sizes of all DB’s hosted on an instance
Function Get-SQLDBSizes
{
param (
[string]$InstaceName = "$(Read-Host ‘Please use format ServerName(IPAddress)\InstanceName’ [e.g. 127.0.0.1\instance])"
)

begin {
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
}
process {
try {
Write-Verbose "Connect to SQL Server using IP address, instance and Windows authentication…"

$dbs = @()

Write-Verbose "Creating SMO Server object…"
$smoServer = new-object Microsoft.SqlServer.Management.Smo.Server $InstaceName

# Use Windows Authentication by setting LoginSecure to TRUE
Write-Verbose "Setting Windows Authentication mode…"
$smoServer.ConnectionContext.set_LoginSecure($True)

# Output object that contains a of the databases
foreach ($Database in $smoServer.Databases) {
$db = New-Object -TypeName PSObject -Property @{
‘DBOwner’ = $Database.Owner
‘DBName’ = $Database.Name
‘DBSize(MB)’ = "{0:N0}" -f $Database.Size
}
$dbs += $db
}
Write-Output $dbs | Format-Table -AutoSize
}
catch [Exception] {
Write-Error $Error[0]
$err = $_.Exception
while ( $err.InnerException ) {
$err = $err.InnerException
Write-Output $err.Message
}
}
}
}#End of function
[/sourcecode]