I needed a way of getting a complete list of databases over multiple instances in SQL Server into something that I was able to manipulate, of course I could load up SSMS and run a query against the MSDB database to return me the name of every database on that instance, but that is slow, I have to connect to each instance I want to query an individually run the code;
SELECT name FROM sys.databases
But that takes too long, especially when you have lots of instances to cycle through. Instead, I decided to use PowerShell and the DbaTools module. With one line of code I was able to get the names of every database out to a CSV file which I could then manipulate.
$instance = 'localhost','localhost\SQL2016' $instance | Get-DbaDatabase -ExcludeSystem | Sort-Object | Select-Object InstanceName,Name | Export-Csv -Path C:\Temp\database.csv
But what does all of this mean?
$instance = 'localhost','localhost\SQL2016'
So here, I am specifying which instances I would like to query with the command
$instance | Get-DbaDatabase -ExcludeSystem
I am then passing that into the Get-DbaDatabase command and telling it with a switch that I want to exclude any system databases, we will assume that every instance has the standard system databases and I don’t need to know about them for every instance I query.
| Sort-Object |
The next part is telling PowerShell that I would like the results ordered, this seems to order the results by database name A-Z.
Now we need to tell PowerShell to only return the information we are interested in, which in my case is the InstanceName & Name which is the database name as it appears in SSMS
Export-Csv -Path C:\Temp\database.csv
Finally, I would like the results to be exported to a CSV file and stored on my local C:\
Pretty neat, this all took 5.1s to run which is far less time that it would have taken me to load up each instance, run the T-SQL query and export the results to CSV.