Show-DatabasesOnServer.ps1
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 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 |
<#PSScriptInfo
.VERSION 1.0 .GUID 48bf0316-66c3-4253-9154-6fc5b28e482a .AUTHOR Rob Sewell .DESCRIPTION Returns Database Name and Size in MB 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 MB for databases on a SQL server .EXAMPLE Show-DatabasesOnServer This will return the user database names and sizes on the local machine default instance .EXAMPLE Show-DatabasesOnServer -Servers SERVER1 This will return the database names and sizes on SERVER1 .EXAMPLE Show-DatabasesOnServer -Servers SERVER1 -IncludeSystemDatabases This will return all of the database names and sizes on SERVER1 including system databases .EXAMPLE Show-DatabasesOnServer -Servers 'SERVER1','SERVER2\INSTANCE' This will return the user database names and sizes on SERVER1 and SERVER2\INSTANCE .EXAMPLE $Servers = 'SERVER1','SERVER2','SERVER3' Show-DatabasesOnServer -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 .NOTES AUTHOR : Rob Sewell http://sqldbawithabeard.com Initial Release 22/07/2013 Updated with switch for system databases added assembly loading and error handling 20/12/2015 Some tidying up and ping check 01/06/2016 #> Function Show-DatabasesOnServer { [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 Name, Size } catch { Write-Error "Failed to get database information from $Server" $_.Exception continue } } else { try { $Return = $srv.databases.Where{$_.IsSystemObject -eq $false}| Select Name, Size } catch { Write-Error "Failed to get database information from $Server" $_.Exception continue } } Write-Output "`n The Databases on $Server and their Size in MB `n" $Return } } |