Script to Pull All Databases (and Properties) on SQL Server Instance with PowerShell Function

It is often asked by support teams investigating issues. It can also be asked by developers checking the impact of other services on their DEV/UAT environments, by change managers investigating impact of changes, by service managers investigating the impact of downtime, when capacity planning for a new service and numerous other situations.

A simple quick and easy question made simpler with this function which can also be called when creating documentation

The Assemblies are getting Loaded by using below script :

$srv = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $Server
($srv.Databases| Get-Member -MemberType Property)

There are 192 Properties that you can examine and that is just for databases:-)

To get the count of Properties use below code :

$srv = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $Server
($srv.Databases| Get-Member -MemberType Property).count

Picking out a few properties you could do something like this

If you want aliases for your column headings you will need to add a bit of code to the select.

For Example, maybe you want to Database Name as a heading and the Size in Gb (Its in Mb in the example above) You would need to create a hash table with a Label element and an Expression element. The Label is the column heading and the Expression can just be the data or a calculation on data.

The Column Heading is Database Name and the data is the Name property and select Size becomes

select @{label=”Database Name”;Expression={$_.Name}}

Select @{label=”Size GB”;Expression={“{0:N3}” -f ($_.Size/1024)}}

The Column Heading is Size GB and the data is the Size property divided by 1024 to 3 decimal places then your code would look like this

$srv.databases|select @{label=”Server”;Expression={$_.Parent.name}}, @{label="Database Name";Expression={$_.Name}}, Owner, Collation, CompatibilityLevel,
RecoveryModel, @{label=”Size GB”;Expression={“{0:N3}” -f ($_.Size/1024)}}|`
Format-Table -Wrap –AutoSize

Here is the Full Script below :

<#PSScriptInfo

.VERSION 1.0

.GUID 48bf0316-66c3-4253-9154-6fc5b28e482a

.DESCRIPTION Returns Database Name and Size in GB for databases on a SQL server
      
.COMPANYNAME 

.COPYRIGHT 

.TAGS SQL, Database, Databases, Size

.LICENSEURI 

.PROJECTURI 

.ICONURI 

.EXTERNALMODULEDEPENDENCIES 

.REQUIREDSCRIPTS 

.EXTERNALSCRIPTDEPENDENCIES 

.RELEASENOTES

#>
<#
    .Synopsis
    Returns the databases on a SQL Server and their size
    .DESCRIPTION
    Returns Database Name and Size in GB for databases on a SQL server
    .EXAMPLE
    Show-DatabasesOnInstance

    This will return the user database names and sizes on the local machine default instance
    .EXAMPLE
    Show-DatabasesOnInstance -Servers SERVER1

    This will return the database names and sizes on SERVER1
    .EXAMPLE
    Show-DatabasesOnInstance -Servers SERVER1 -IncludeSystemDatabases

    This will return all of the database names and sizes on SERVER1 including system databases
    .EXAMPLE
    Show-DatabasesOnInstance -Servers 'SERVER1','SERVER2\INSTANCE'

    This will return the user database names and sizes on SERVER1 and SERVER2\INSTANCE
    .EXAMPLE
    $Servers = 'SERVER1','SERVER2','SERVER3'
    Show-DatabasesOnInstance -Servers $servers|out-file c:\temp\dbsize.txt

    This will get the user database names and sizes on SERVER1, SERVER2 and SERVER3 and export to a text file c:\temp\dbsize.txt
       
#>

Function Show-DatabasesOnInstance
{
    [CmdletBinding()]
    param (
        # Server Name or array of Server Names - Defaults to $ENV:COMPUTERNAME
        [Parameter(Mandatory = $false, 
            ValueFromPipeline = $true,
            ValueFromPipelineByPropertyName = $true, 
            Position = 0)]
        $Servers = $Env:COMPUTERNAME,
        # Switch to include System Databases
        [Parameter(Mandatory = $false)]
        [switch]$IncludeSystemDatabases
    )
    [void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.Smo" );
    foreach ($Server in $Servers) {
        if ($Server.Contains('\')) {
            $ServerName = $Server.Split('\')[0]
            $Instance = $Server.Split('\')[1]
        }
        else {
            $Servername = $Server
        } 

        ## Check for connectivity
        if ((Test-Connection $ServerName -count 1 -Quiet) -eq $false) {
            Write-Error "Could not connect to $ServerName - Server did not respond to ping"
            $_.Exception
            continue
        }
    
        $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Server

        if ($IncludeSystemDatabases) {
            try {
                $Return = $srv.databases|select @{label="Server";Expression={$_.Parent.name}},` 
@{label="Database Name";Expression={$_.Name}}, Owner, Collation, CompatibilityLevel,` 
RecoveryModel, @{label="Size GB";Expression={"{0:N3}" -f ($_.Size/1024)}}|` 
Format-Table -Wrap –AutoSize
            }
            catch {
                Write-Error "Failed to get database information from $Server"
                $_.Exception
                continue
            }
        }
        else {
            try {
                $Return = $srv.databases.Where{$_.IsSystemObject -eq $false}| select @{label="Server";Expression={$_.Parent.name}},` 
@{label="Database Name";Expression={$_.Name}}, Owner, Collation, CompatibilityLevel,` 
RecoveryModel, @{label="Size GB";Expression={"{0:N3}" -f ($_.Size/1024)}}|` 
Format-Table -Wrap –AutoSize
            }
            catch {
                Write-Error "Failed to get database information from $Server"
                $_.Exception
                continue
            }
        }
        Write-Output "`n The Databases on $Server and their Size in GB `n"
        $Return
    }
}

After creating the Function ‘ Show-DatabasesOnInstance ‘ , execute on powershell as shown below :

You can modify the script based on your requirement with the help of Database Properties as discussed in above (we have almost 192 Properties).

Script can be changed\ modified at 2 places in Function where ” $Return ” value is mentioned.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.