You have two options, both of which will require SQL Server Management Studio to be installed on the host you are running the commands from:
- SQLPS
- SMO
I lean toward the latter because it includes a bit easier format to learn for basic task such as getting a list of objects in an instance.
With SMO you have to load the appropriate assemblies you want to work with. In most cases Microsoft.sqlserver.management.smo.server
will be the most common one used.
With SQLPS you have to know what version of SQL Server you are working with. SQL Server 2008 R2 (and R1) you will add-pssnapin *sql*
and SQL Server 2012 and beyond you will import-module SQLPS
.
Now to the question at hand of getting a list of databases.
SQLPS method
dir SQLSERVER:\\SQL\ServerName\Default\Databases | select name
The above would be for a default named instance, you would change "default" to your instance name if it was a named instance.
SMO method
$srv = New-Object 'Microsoft.SqlServer.Management.SMO.Server' "myInstance"
$srv.Databases | select name
Now the above code can easily be wrapped into a function or for each loop that would allow you to easily pass in multiple server names.
Edit
Depending on what literature you read on PowerShell you will find that most folks that take time to write out a script, will go ahead and spend the time to make it a repeatable one. This is done either through a function (basic level for me) and then modules (more advanced that I have not touched yet).
So for your example code:
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") "MSQLULTDBS04"
foreach($sqlDatabase in $sqlServer.databases) {$sqlDatabase.name}
Your foreach
loop is not needed in order to output the list of databases. This is an object within SMO that contains multiple properties, one of which is name
and PowerShell will simply output that in the default format, as shown below:
A difference to note between SMO and SQLPS here is that SQLPS would not output the system databases by default, SMO will.
If you look up blog post on PowerShell Profiles a common thing to do is add the assembly command to that so ever time you open your PowerShell command it is already available. I will then add functions that are basically commands I use often, and happen to have one for databases:
function Get-DBList ($server)
{
$srv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $server
$srv.Databases | Select name, RecoveryModel,
@{Label="CompatibilityLevel";Expression={($_.CompatibilityLevel).ToString().Replace("Version", "")}}
} #end Get-DBList
You can modify this to your liking but if I call it using my local instance I get the output below.
I will go ahead and let you know something that I found out the hard way. Using SMO you can connect to SQL Server 2000 up to SQL Server 2014, however, each version of SQL Server can vary on where particular properties are located. I had a learning conversation with Jonathan Kehayias on Twitter one afternoon learning this but I cannot recall what property I was looking for. In these situations the .NET class library on MSDN for SMO is your friend because it can help guide you to properties you are looking for.
Best Answer
https://support.timextender.com/hc/en-us/articles/210437663-Error-Could-not-load-file-or-assembly-Microsoft-SqlServer-ConnectionInfo-
Cause
You have not installed the SQL Shared Management Objects (SMO). They are needed to create tables and other SQL objects from TX.
Resolution
Download the SQL Shared Management Objects from the SQL Server Feature Packages and install it. The download sites are linked below - the file you need is called SharedManagementObjects.msi.
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
SQL Server 2005
SQL Server 2017
Many thanks to Tobias Eld and my apologies to the net for not finding this resource before I posted this question.