SMO comes with a hierarchy of objects that are accessible programmatically. For example, when we create an SMO server variable, we can then access databases, logins, and database level triggers. Once we get a handle of individual databases, we can then traverse the tables, stored procedures and views that it contains. Since many tasks involve SMO objects, you will be at an advantage if you know how to discover and navigate these objects.
Open up your PowerShell console, PowerShell ISE, or your favorite PowerShell editor.
You will also need to note what your instance name is. If you have a default instance, you can use your machine name. If you have a named instance, the format will be <machine name>\<instance name>.
In this recipe, we will start exploring the hierarchy of objects with SMO:
Import the SQLPS module as follows:
Import-Module SQLPS -DisableNameChecking
Create a server instance as follows:
$instanceName = "localhost" #code below all in one line $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
Get the SMO objects directly accessible from the
$serverobject:$server | Get-Member -MemberType "Property" | Where-Object Definition -Like "*Smo*"Now, let's check SMO objects under databases:
$server.Databases | Get-Member -MemberType "Property" | Where-Object Definition -Like "*Smo*"To check out the tables, you can type and execute the following:
$server.Databases["AdventureWorks2014"].Tables | Get-Member -MemberType "Property" | Where-Object Definition -Like "*Smo*"
SMO contains a hierarchy of objects. At the very top there is a server object, which in turn contains objects such as Databases, Configuration, SqlMail, LoginCollection, and so on. These objects in turn contain other objects, for example, Databases is a collection that contains Database objects, and a Database contains Tables.
Note
You can check out the SMO Object Model Diagram from the MSDN at https://msdn.microsoft.com/en-ca/library/ms162209.aspx.
One way to navigate through the hierarchy is by creating a server instance first. From here, you can use Get-Member to figure out which properties belong to that object. Once you find out, you can start creating additional variables for the member objects and then use Get-Member on them. Lather, rinse, and repeat.