Powershell script to get list of databases on a server

At one of my clients I received an email from one of the IT Project Managers asking a simple question:

"Can you please let us know which databases reside on the server below, Server1?"

First thought in mind, well from what particular sql instance on that server? At that point I was not even sure if that server has multiple instance, is it a stand alone sever or a node/virtual name of a cluster server, alwayson cluster etc...

But I keep that thought to myself.

Now, I could launch SSMS, connect to the sql instance, query the sys.databases and get requested information.

But I don't know the instance name top of my head. So I would need to RDP into the server or look up the meta data somewhere.

Instead of that, I decide to launch the Powershell and issue this command:

Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName "Server1"

It has only once sql instnace, great.

Then I issued the following command to grab the databases on that list and send him the results.

Get-SqlDatabase -ServerInstance Server1

That last command requires the SqlServer module loaded, which I already have in my powershell startup.

Import-Module -Name SqlServer

But to add bit more value to this blog, I decided to turn this into a small powershell script.

PS variable $server_name is where you specify the server  name where you would like to look up database names. If that value is not specified, it will use the local computer name.

try
{
Import-Module -Name SqlServer
$server_name = "Server1"

# if $server_name string is empty or null then use the current computer name
if ($server_name -eq "" -or $server_name -eq $null)
        {
$server_name = $env:computername
        }
"Looking up SQL Services on: $server_name"
$sql_services = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName "$server_name" -ErrorAction Stop

if ($sql_services.PSComputerName -ne $null)
        {

                # Display sql services on the screen

                $sql_services  |  select-object -Property PSComputerName, @{n="sql_instance";e={$_.Name -replace "MSSQL$", ""}}, Name, ProcessID, StartMode, State, Status, ExitCode, PathName | ft -AutoSize

foreach ($sql_service in $sql_services) 
                {
""
$instnace_name = $sql_service.Name -replace "MSSQL$", ""
"Instnace Name: " + $instnace_name
if ($instnace_name -eq "MSSQLSERVER") 
                        {
$sql_connection = $sql_service.PSComputerName
                        }
else 
                        {
$sql_connection = $sql_service.PSComputerName + "" + $instnace_name
                        }
$sql_connection + ": " + $sql_service.State
if ($sql_service.State -eq "Running")
                        {
Get-SqlDatabase -ServerInstance $sql_connection
                        }
else
                        {
"Skipping $sql_connection as its not running..."
                        }
                }
        }
}
Catch
{
        (Get-Date).ToString() + ": Error Occurred" 
        throw $_  
return
}

I have done some testing but a thorough testing.  Please test and let me know your thoughts/comments.

Also, I have not tested this on sql servers hosted in Linux.