Recently in SQL DMO Category

A selection of useful DBCC commands for SQL Server. Not looked in any great detail, but it seems to cover the generally useful ones.

Whilst you could just execute the following TSQL command:

select name from master..sysdatabases

Dim oSqlServer as SQLDMO.SQLServer2 = New SQLDMO.SQLServer2Class
Dim oSqlServerDB as SQLDMO.Database

For Each oSqlServerDB In oSqlServer.Databases
MessageBox.Show(oSqlServerDB.Name)
Next

I've used MessageBox.Show to output the database names, but I could've just as easily have pushed the details of each database into an ArrayList or other collection and returned the information as the output of a function. Another way of writing the above code (not as easy to read it has to be said! Well, not as "OOP" anyway!) is:

For i As Integer = 1 To m_oSqlServer.Databases.Count
MessageBox.Show(m_oSqlServer.Databases.ItemByID(i).Name)
Next

And that, pretty much, is how to enumerate the databases on a SQL Server via SQLDMO

Using SQLDMO within .net isn't the easiest thing to find information about, so here goes with some documentation.

The first thing that you need to do is add a reference to the SQLDMO library. With .net, you do this by: (when within a project)

1 - Choose "Project" from the menubar and then "Add Reference"
2 - In the dialog that appears, choose the "COM" tab
3 - Choose (double click) the entry named "Microsoft SQLDMO Object Library", it will then appear in the listbox at the bottom of the dialog. ("Selected Components")
4 - Click OK

Note: If the SQLDMO entry is not present, then you likely don't have SQLDMO installed on your machine, or if you do its COM registration is in some way corrupt. Try re-installing.

In the "References" node of Solution Explorer, there will now be a new entry called "Interop.SQLDMO". You're all set now to start using SQLDMO in your code. It's worth noting that SQLDMO seems to have problems if you put an "Imports SQLDMO" directive in your code, so don't do it.