Powershell and SQL Server – 1- List Databases and Size from Multiple SQL Server Instance with SMO

It’s quick to pull Database List with Size,Database Space Usage,Index Space Usage and Free Space with in databases using SMO with Powersehll.

$SQLserverDB=@() 
$SQLserverDB=Get-Content "C:\PowershellScript\ServerList.txt"
    foreach($SqlServer in $SQLserverDB)
    {
        Write-Host "Server :"$SqlServer -ForegroundColor Yellow
        $server = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer)
        $server.Databases | select Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable | ft
    }

SMO1

You can use SSMS Object explorer to get the database, Size, Database Space Usage,Index Space Usage and Free Space

SMO1_SSMS

2 thoughts on “Powershell and SQL Server – 1- List Databases and Size from Multiple SQL Server Instance with SMO

  1. Hi
    Geeting below error while executing script

    New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that
    the assembly containing this type is loaded.
    At E:\DBA_test\DBMirror\Mirror_New.ps1:6 char:19
    + $server = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServe …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectComman
    d

    1. Hi Jaydeep,

      You need to import sqlps module before executing the script

      import-module sqlps

      If you don’t have sqlps module installed, use the below command to install the same

      Import-Module Sqlps

Leave a Reply

Your email address will not be published. Required fields are marked *