Get inventory of SQL databases on all servers

Despite the title of this page,  the powershell script here does not discover/scan all servers within your network to see which ones have sql server installed and then gather list of databases from all those....  Nope.

It does however allows you to give a specific server name, list of servers or even a text file containing server names to search for sql server databases. 

It makes few assumptions:

1. You have access to the servers
2. You have access to the sql server instances on them
3. You are using your currently logged in domain account to access the servers as well as sql server instances

In other words, I have not coded it a way that would have allowed you to specify different credentials for different servers and sql instances.

So here is the powershell script:

#        Remove-Variable -Name * -ErrorAction SilentlyContinue

        Import-Module -Name SqlServer

#       OPTIONAL VARIABLES
        $server_list   = @("Server1")   # SERVER/HOST NAME HERE. IF THIS IS EMPTY THEN WE WILL ASSUME THE CURRNET COMPUTER NAME

        #### Notice that the $server_list is an array variable so you could specify multipl servers here, each separated by a comma or import the server names from a text file

#        $server_list   = @("ServerA", "ServerB")
#        $server_list   = get-content 'servers.txt' | sort -uniq # Read the server list and remove any duplicates

        $database_name = ""                  # DATABASE NAME YOU WOULD LIKE TO SEARCH OTHERWISE LEAVE THIS BLANK
        $exact_match   = "Y"                 # WHETHER TO SEARCH FOR AN EXACT DATABASE NAME, IGNORED IF $database_name IS EMPTY
        $export_to_excel_file = ""           # FILE NAME TO EXPORT THE DATABASE LIST TO OTHERWISE LEAVE THIS BLANK. 
                                             # IF A FILE WITH THIS NAME ALREADY EXIST THEN IT WILL BE REPLACED.
        
#        $export_to_excel_file = "$env:TEMP\sql_database_list.XLSX"


        # if $server_list string is empty or null then use the current computer name
        if ($server_list -eq "" -or $server_list -eq $null)
        {
                $server_list = $env:computername
        }


        "Total Number of Servers: " + $server_list.Count
        "Looking up SQL Services on: $server_list"

        $sql_services = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName $server_list -ErrorAction SilentlyContinue
        

        $all_databases = @()      # Declare an empty array to store list of the databases
        $sql_connections = @()

        ## Build an array with list of sql instances that are in RUNNING state
        # Named SQL Instances
        $sql_connections +=  $sql_services | Where-Object {$_.State -eq 'Running' -and $_.Name -ne 'MSSQLSERVER'} | select `
                                             @{n='SQL Server'; e={"{0:n0}" -f ($_.PSComputerName)}},
                                             @{n="FQDN";e={[System.Net.Dns]::GetHostEntry($_.PSComputerName).HostName}},
                                             @{n='Instance Name'; e={"{0:n0}" -f ($_.Name)}},
                                             @{n='SQL Connection'; e={"{0:n0}" -f ($_.PSComputerName + '\' + $_.Name -replace 'MSSQL\$', '')}}
        # Default sql instances
        $sql_connections += $sql_services | Where-Object {$_.State -eq 'Running' -and $_.Name -eq 'MSSQLSERVER'} | select `
                                            @{n='SQL Server'; e={"{0:n0}" -f ($_.PSComputerName)}},
                                            @{n="FQDN";e={[System.Net.Dns]::GetHostEntry($_.PSComputerName).HostName}},
                                            @{n='Instance Name'; e={"{0:n0}" -f ($_.Name)}},
                                            @{n='SQL Connection'; e={"{0:n0}" -f ($_.PSComputerName)}}
        

        "Total Number of SQL Instances: " + $sql_connections.Count
        $sql_connections | select -Property 'SQL Connection'
        ""
        "Looking up databases..." 

         if ($database_name -eq "" -or $database_name -eq $null)    # All databases
         {
                "Get list of all databases..."
                $databases = Get-SqlDatabase -ServerInstance $sql_connections.'SQL Connection' 
         }

        else    
        {
                if ($exact_match -eq "Y")                           # Exact match
                {
                        "Exact match is enabled..."
                        "Looking for database with exact name: $database_name"
                        $databases = Get-SqlDatabase -ServerInstance $sql_connections.'SQL Connection' | where {$_.name -eq $database_name}
                }
                else                                                # Like/contains search
                {
                        "Exact match is disabled...."
                        "Looking for any database containing $database_name in the name...."
                        $databases = Get-SqlDatabase -ServerInstance $sql_connections.'SQL Connection' | where {$_.name -like "*$database_name*"}
                }
        }


        $all_databases = $databases | select -Property @{n='SQL Instance'; e={"{0:n0}" -f ($_.Parent)}}, `
                         @{n='Database Name'; e={"{0:n0}" -f ($_.Name)}}, Status, RecoveryModel, Size, SpaceAvailable, Owner, `
                         CompatibilityLevel, Collation, ContainmentType | sort -Property 'SQL Instance', 'Database Name'

        ""

        "Total number of databases found:" + $all_databases.Count

        if ($all_databases.Count -lt 1000 -or $export_to_excel_file -eq "")
        {
                "Display database list on the screen..."   
                $all_databases | ft -AutoSize
        }
        if ($export_to_excel_file -ne "")
        {
                "Exporting to $export_to_excel_file...." 
                Remove-Item $export_to_excel_file -ErrorAction SilentlyContinue -WarningAction SilentlyContinue -Force
                $sql_connections | Export-Excel -Path $export_to_excel_file  -WorksheetName 'SQL Instances' -TableName instances -TableStyle Medium6
                $all_databases | Export-Excel -Path $export_to_excel_file  -WorksheetName Databases -TableName databases -TableStyle Light9
        }

Hopefully you will find this useful.