January 12, 2009
Posted by on
SQL Server Management Objects is a set of .NET objects from Microsoft. This replaced DMO (Distributed Management Objects) in 2005. You can use SMO to communicate with a SQL Server the same way you do with Management Studio. For example, you can list all tables, script them, find your permissions+++. You even use it to communicate with SQL Server Agent to make jobs etc!
The last days I’ve been working on a little application we’ll use to script over a database automatically. First we’ll delete all a-objects, then we’ll script them in from another database.
I’ve made an example project, so you can see how easy it is. First connect using a ServerConnection, and then setting the gServer. With gServer I can access any object I want on the server. To access a table, you use: gServer.Databases(“YourDatabaseName”).Tables(“YourTableName”)
To generate all my scripts, I’m looping the Script-method on the Tables property. I add a parameter vOptions so I only get what I want (dependent on which checkboxes are checked.
Click here to download the test project.