$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
    }

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

   Reply

   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

      Reply