Taking SQL Server Inventory with powershell

Work around databases long enough, sooner or later you or your dba will get asked something to the effect "hey, how many sql databases do we have?". There are ways of doing this without buying a really expensive tool, but then again these expensive tools make things easy. If you find yourself in this situation, there are ways of finding each and every instance of SQL server on your network.  Well, at least those that are "reachable" via the network and wmi.  If wmi isnt enabled on a machine, using a tool like sqlping will be needed. 
The powershell script below will run over each machine on your network, look for an instance of sql server, then get a list of databases on that instance and then dump these results to a csv file. If you have a medium to large network this could take a while.

Update:
I like this method too
https://www.mssqltips.com/sqlservertip/2013/find-sql-server-instances-across-your-network-using-windows-powershell/

       

 <#    
 Query from a list of machines (either pre-defined or queried from AD)  
 Iterate over the list and interrogate SQL for its information - version etc  
 For each instance, get a comma-seperated list of databases that are visible  
 Note:   
 You will need elevated permissions to run this.  
 SqlServer powershell module required  
 see here https://docs.microsoft.com/en-us/sql/powershell/sql-server-powershell #>  
   
   
 #Requires -version 3.0  
 #add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement,   
   
 version=11.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91";  
 #add-type -AssemblyName "Microsoft.AnalysisServices, version=11.0.0.0,   
   
 Culture=Neutral, PublicKeyToken=89845dcd8080cc91";  
 Import-Module SqlServer   
   
 $data_table = New-Object "system.data.datatable";  
 $col = New-Object "system.data.datacolumn" ('MachineName', [System.String]);  
 $data_table.columns.Add($col);  
 $col = New-Object "system.data.datacolumn" ('ServerInstance', [System.String]);  
 $data_table.columns.Add($col);  
 $col = New-Object "system.data.datacolumn" ('Type', [System.String]);   
   
 #type=SQLServer / AnalysisServer / ReprtServer / IntegrationService   
 $data_table.columns.Add($col);  
 $col = New-Object "system.data.datacolumn" ('Version', [System.String]);  
 $data_table.columns.Add($col);  
 $col = New-Object "system.data.datacolumn" ('Edition', [System.String]);  
 $data_table.columns.Add($col);  
 $col = New-Object "system.data.datacolumn" ('ServiceAccount', [System.String]);  
 $data_table.columns.Add($col);  
 $col = New-Object "system.data.datacolumn" ('DatabaseList', [System.String]);  
 $data_table.columns.Add($col);  
   
 #-- Get a list of all machines on the domain  
 [string[]]$server_list = Get-ADComputer -Filter * -Property * |  Select-Object -ExpandProperty Name   
    
 #-- [string[]]$server_list = gc -path 'c:\temp\server_list.txt' #you can put your server list in a text file, each [ServerName] uses one line  
   
   
   
 foreach ($machine_name in $server_list)  
 {    
   
   
 "processing : $machine_name";  
   try   
   {  
    
  #-- for current instance, get metrics such as sql edition and service account that is running this instance  
     $mc = new-object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $machine_name;  
     $mc.services | ? {($_.type -in ("SqlServer", "AnalysisServer", "ReportServer", 'SqlServerIntegrationService') ) -and ($_.ServiceState -eq 'Running')} | # % {($_.name).contains("$")}  
     % { $s = $_.name;  
       [string]$svc_acct = $_.ServiceAccount;  
       switch ($_.type)   
       { "sqlserver" { if ($s.contains("$")) {$sql_instance= "$($machine_name)\$($s.split('$')[1])"} else {$sql_instance=$machine_name;}   
                 $sql_svr = new-object "microsoft.sqlserver.management.smo.server" $sql_instance;  
                 $row = $data_table.NewRow();  
                 $row.Edition = $sql_svr.Edition;   
                 $row.Version = $sql_svr.Version;  
                 $row.Type = 'SQLServer';  
                 $row.ServerInstance = $sql_instance;  
                 $row.ServiceAccount = $svc_acct;  
                 $row.MachineName=$machine_name;  
                 #$row.DatabaseList = $server_list-join ',';  
   
                 $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sql_instance;  
                 $dbs=$s.Databases ;      
   
                 $row.DatabaseList = $dbs-join ', ';  
   
                 $data_table.Rows.Add($row);    
                   
   
              } #sqlserver   
   
                        
                 
       }#switch  
     }  
   }#try  
   catch  
   {  
     Write-Error "Error - cant get info for $machine_name" #--$Error[0].Exception  
   }  
 }  
   
 #-- Lets see the results  
 #--$data_table | select machineName, serverinstance, type, version, edition | ft -auto  
   
 #if you want to export to an csv file, you can do the following, assuming you have c:\temp\ folder  
 $data_table | select machineName, serverinstance, type, version, edition, DatabaseList | export-csv -path d:\temp\sql-db-instance-04112018.csv -notypeinfo -Force