List SQL Databases per Instance on a SQL Server

Once in your lifetime you might need to know how many databases a server has – if not your best fiend will surely ask you :-). Then it’s time to meet SQL Server Management Objects (SMO).

Another trick is how to identify Instance name, in this case I decided to read the SQL Service name using WMI.

This simple script reads a servers list from a txt file, identify the SQLServer Instance and once connected it shows every Database name, size and status. Feel free to use it.

##############################################################
# List Databases per SQL Instance based on SQL ServerName List
##############################################################
cls
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
# Load Servers from File
$Servers = get-content “SQLServers.txt”
# Identify SQL Instances
ForEach($Server in $Servers)
{
$Instances = Get-WmiObject -ComputerName $server win32_service | where {$_.name -like “MSSQL*”}
ForEach ($Instance in $Instances)
{
#Remove “MSSQL$” Prefix
$InstanceName=($Instance.name).replace(“MSSQL$”,””)
Try
{
# Identify BBDD
$ServerInstance = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “$Server\$InstanceName”
$Dbs=$ServerInstance.Databases
Write-Host “Checking Instance: ” $InstanceName -ForeGroundColor Yellow
Write-Host “———————————”
$Dbs | select name, size, status | ft -autosize
Write-Host “_________________________________”
}
Catch
{
write-host “Ooops ! Can not get connected …”
}
}
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s