Although SQL Server Management Studio (SSMS) is a great tool to manage a SQL Server instance there might be a need to manage your SQL Server instance programmatically.
For example, consider you are developing a build deployment tool, this tool will deploy the build but before that it needs to make sure that the SQL Server and SQL Server Agent services are running, a database is available and online. For this kind of work, you can use SMO, a SQL Server API object model.
The SMO object model represents SQL Server as a hierarchy of objects. On top of this hierarchy is the Server object, beneath it resides all the instance classes.
SMO classes can be categorized into two categories:
Instance classes - SQL Server objects are represented by instance classes. It forms a hierarchy that resembles the database server object hierarchy. On top of this hierarchy is Server and under this there is a hierarchy of instance objects that include: databases, tables, columns, triggers, indexes, user-defined functions, stored procedures etc. I am going to demonstrate the usage of a few instance classes in this tip in the example section below.
Utility classes - Utility classes are independent of the SQL Server instance and perform specific tasks. These classes have been grouped on the basis of its functionalities. For example Database scripting operations, Backup and restore databases, Transfer schema and data to another database etc. I will discussing the utility classes in my next tip.
How SMO is different from SQL-DMO
SMO object model is based on managed code and implemented as .NET Framework assemblies. It provides several benefits over traditional SQL-DMO along with support for new features introduced with SQL Server 2005 and SQL Server 2008.
It offers improved performance by loading an object only when it is referenced, even the objects properties are loaded partially on object creation and left over objects are loaded only when they are directly referenced.
It groups the T-SQL statements into batches to improve network performance.
It now supports several new features like table and index partitioning, Service Broker, DDL triggers, Snapshot Isolation and row versioning, Policy-based management etc.
An exhaustive list of the comparisons between SQL-DMO and SMO can be found here.
Before you start writing your code using SMO, you need to take reference of several assemblies which contain different namespaces to work with SMO. To add a reference of these assemblies, go to Solution Browser - > References -> Add Reference.
Add these commonly used assemblies.
Microsoft.SqlServer.Management.Sdk.Sfc.dll // on SQL Server/VS 2008 only