Exploring SMO Server Objects

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.

Getting ready

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>.

How to do it...

In this recipe, we will start exploring the hierarchy of objects with SMO:

1. Import the SQLPS module as follows:

   Import-Module SQLPS -DisableNameChecking

2. 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

3. Get the SMO objects directly accessible from the $server object:

   $server |
   Get-Member -MemberType "Property" |
   Where-Object Definition -Like "*Smo*"

   Note

   If you are using PowerShell v2, you will have to change the Where-Object cmdlet usage to use the curly braces {} and the $_ variable:

   Where-Object {$_.Definition -like "Smo*" }

4. Now, let's check SMO objects under databases:

   $server.Databases |
   Get-Member -MemberType "Property" |
   Where-Object Definition -Like "*Smo*"

5. 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*"

How it works...

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.

See also

• The recipe Loading SMO assemblies.
• The recipe Creating a SQL Server Instance Object.