Exploring SMO server objects

SQL Management 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, the 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 = "KERRIGAN"
   
   $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 Definition -like "*Smo*"

4. Now let's check SMO objects under databases. Execute the following line:

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

5. To check out the tables, you can type and execute:

   $server.Databases["AdventureWorks2008R2"].Tables | 
   Get-Member -MemberType "Property" | 
   Where 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 the like. These objects in turn contain other objects, for example, Databases is a collection that contains Database objects, and a Database in turn, contains Tables and so on.

See also

• The Loading SMO assemblies recipe

• The Creating a SQL Server instance using SMO recipe

• You can also check out the SMO object model diagram from MSDN:

  http://msdn.microsoft.com/en-us/library/ms162209(SQL.110).aspx