How to get list of databases from multiple servers using Powershell script

How to get list of databases from multiple servers

 Sounds very easy then done. When some one ask you to get a list of databases from server we do remember “sys.databases” and if some one ask from 5-10 servers we connect to each instance in sql server and get the list of databases. Query will be like

“Select name as [database name] ,@@servername as [server name]from sys.databases”

But what if some one asks you to get list of databases from 100+ servers. It will take days to finish manually but powershell can get this done in a click

So how to do this

1. First find a server in domain having powershell installed
2. Create directory “Drive:\Monitoring_Automation\”
3. Create files in above directory

                  a.     Serverlist  
                    b.     Powershell script
                    c.      Batch file to call PS1 file
                    d.     Output file to collect list of database

4. In “Drive:\Monitoring_Automation\Servers.txt” write all server name separated with enter from which you want to get database list

5. In “Drive:\Monitoring_Automation\ dblist.ps1” place below code

/*********** Code Start****************/

#File name : Drive:\Monitoring_Automation\dbdetail.ps1
#Initializing output path
#$FilePath = "Drive:\Monitoring_Automation\"
#$OutFile1 = Join-Path -path $FilePath -childPath ("dblist_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".log")
$OutFile = "Drive:\Monitoring_Automation\output.csv"

#Running code on each server

foreach ($svr in get-content "X:\Monitoring_Automation\Servers.txt")
{
  $conn = "server=$svr;database=master;Integrated Security=sspi"
  $cmd = "select name , @@servername from sys.databases"
  $DataAdapter = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $conn)
  $DataTable = new-object System.Data.DataTable
  $DataAdapter.fill($DataTable) | out-null
  $svr
  $DataTable | Format-Table -autosize
            #$DataTable | export-csv -noType $OutFile1
            $DataTable  >>$OutFile
}
     #$DataTable | export-csv -noType $OutFile1
     $end = get-date 
     write-host "End: "  $end
/*****************ENd of Code***********************/

6. In “Drive:\Monitoring_Automation\ GetDBlist.bat”  place below code

/************** CODE START **************/

Powershell.exe  “ Drive:\Monitoring_Automation\dbdetail.ps1”

/************* CODE END *******************/

7. If you will not even create “Drive:\Monitoring_Automation\output.csv” , powershell will take care of step

8. Now open command prompt : Run -> CMD -> Go to Drive:\Monitoring_Automation\

9. Once execution is completed, you will get output in command prompt screen and in “Drive:\Monitoring_Automation\output.csv” as well.

10.  Copy the output in excel , use data -> text to column option to format and your report is ready to be sent