Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

Data Management Objects: Other Database Objects

Last updated Mar 28, 2003.

We've covered quite a bit of ground in the DMO articles I've posted here in the last few weeks. I've explained how to work with many database objects, and in the last few articles I've spent some time showing you how to work with Transact-SQL statements.

In this article, I continue the tour, with a few more pointers on working with other objects in the database. I explain how to use these objects, and how you can put them in any of the code frameworks I've covered previously.

You're probably familiar with that framework by now: You create a new SQL DMO server and any objects you're interested in, connect to them, work with them and then close them out. You also know that certain object groups are called "collections," (such as SQL Servers), and others are the base objects that you can work with (such as a particular table). Having these collections and discrete objects available in code makes DMO programming very powerful. In fact, you can do just about anything to your server and its objects using SQL Server DMO programming. You can, but that doesn't mean you should.

What I mean is that certain activities are better suited to DMO programming than are others. For instance, SQL DMO is very easy to code for managing servers and documenting information, but it's less friendly for creating new database objects. I've focused this series of articles on the more practical applications of DMO code, such as gathering object information, performing maintenance, and running and logging T-SQL queries. I believe these types of activities are worth the time of learning SQL DMO programming.

I'll put these examples into a few categories where I think DMO shines. First, there's object documentation. As I showed you in the first few articles, you can read many properties of the server and its objects. Sure, you can get the same information using T-SQL, but DMO allows you to connect to multiple servers quickly, and allows access to the file system. This allows you to document many servers in one click.

Next, there are the maintenance functions. SQL DMO gives you the ability to update statistics, perform database backups and more. Once again, you can connect to multiple servers and document the process along the way.

Another category of useful DMO programming is running T-SQL statements from a "feeder" file and creating output files.

In the next few weeks, I'll show you two more categories where DMO is very useful: security and scripting.

In this article, let's talk about a few more of the objects you can use in many of these categories. Since they didn't fit neatly into one single category, I thought it best to cover them here, in the middle.

Let's take a look at that basic framework once again:

Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.LoginSecure = True
oServer.Connect "(local)"
' Get and work with objects here
Set oServer = Nothing

The other day, I needed to determine if I had the custom data type defined in all my databases, so I created some code that uses a collection on the database to list them:

Set oUDT = CreateObject("SQLDMO.UserDefinedDatatype")
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.LoginSecure = True
oServer.Connect "(local)"

For Each oUDT in oServer.Databases("pubs").UserDefinedDataTypes
  strMsgText = strMsgText & oDT & VBCRLF
Next 

MsgBox strMsgText
Set oUDT = Nothing
Set oServer = Nothing

You can use the same process to find triggers, constraints, and anything that requires you to hop around to find in other tools. By using a server and database collection above the database collection, you can also iterate through all your servers and databases to make sure that you've kept a consistent naming scheme on various objects.

That's really the crux of working with DMO. I've had some comments from other DBAs who ask me, "I can do all this with T-SQL. What's the point?" That's a fair question.

The answer is in the ability to deliver an application that can iterate through the servers and databases and create a consolidated method to collect that information. To be sure, you can do much the same in T-SQL, but that requires T-SQL knowledge and a client program (such as osql) for the user. With a DMO program, you can deliver a simple, consistent, graphical interface to anyone.

Let's look at another example. Along with information about customizations I made to a database, I often need to check up on the automation I put into place. SQL Server provides two basic methods to do that: jobs and alerts. To find out about them, use a property of the Server object that controls the job behavior in SQL Server, called (appropriately enough) JobServer. Using the For Each iteration method, here's some code that gets the names of the all of the jobs in a SQL Server:

Dim oJob 

Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.LoginSecure = True
oServer.Connect "(local)"

For Each oJob in oServer.JobServer.Jobs
  strMsgText = strMsgText & oJob.Name & VBCRLF
Next 

MsgBox strMsgText
Set oServer = Nothing

Once you have that object, there's a lot more you can do with it. See the full documentation I reference at the end of this article for more properties and methods on the jobs object.

For some properties, such as schedules, you need to reference a particular job, like this:

oJob("JobName").JobSchedules

The alerts object uses the JobServer property as well:

Dim oAlert 

Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.LoginSecure = True
oServer.Connect "(local)"

For Each oAlert in oServer.JobServer.Alerts
  strMsgText = strMsgText & oAlert.Name & VBCRLF
Next 

MsgBox strMsgText
Set oServer = Nothing 

SQL DMO provides access to just about any object you need to control or document on your server.

In the next two articles, I'll finish up this series on SQL DMO programming with two very useful examples: security and scripting.

Online Resources

The full documentation for the SQL Server Object Library can be found here.

You can learn more about SQL Server options over at Windows IT Pro – the full article is here.