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.
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>
In this recipe, we will start exploring the hierarchy of objects with SMO.
Import the
SQLPSmodule as follows:Import-Module SQLPS -DisableNameChecking
Create a server instance as follows:
$instanceName = "KERRIGAN" $server = New-Object ` -TypeName Microsoft.SqlServer.Management.Smo.Server ` -ArgumentList $instanceNameGet the SMO objects directly accessible from the
$serverobject:$server | Get-Member -MemberType "Property" | Where Definition -like "*Smo*"
Now let's check SMO objects under databases. Execute the following line:
$server.Databases | Get-Member -MemberType "Property" | Where Definition -like "*Smo*"
To check out the tables, you can type and execute:
$server.Databases["AdventureWorks2008R2"].Tables | Get-Member -MemberType "Property" | Where 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 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.
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