SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Adding a Column using SQL_DMO

By Andy Warren,

Recently SqlServerCentral reader Judith Loughran posted a question in our DMO forum asking for some help in altering a table. Since I hate to miss an opportunity to evangelize for DMO, here is a code sample showing how to add a column to the categories table in the Pubs database. For those of you who may be interested I have several other articles on DMO with additional code samples posted here on the site.

One comment I frequently hear about DMO is "why use it? T-SQL can do that!". Well, it's certainly true that there isn't much if anything that you can do in DMO that you can't do using T-SQL and/or Enterprise Manager. DMO will typically appeal to a developer more than a DBA since it is an "object model". If you understand how classes, collections, and object hierarchies work, using DMO is pretty straight forward.

A key concept about DMO that you need to learn is that you'll almost always add an object (a class). This is a little different than you may be used to in VB where the add method (such as ocolumns.add - add is the method) is parameterized to save you the extra step of instantiating (creating) an object. For example, here is how the add might look if built using parameters:

The only difference is where the work gets done. Using DMO you create the object and pass it in as single parameter. Of course you can (and might) do this in VB as well, but more likely you'll go with parameters - so what does VB do with the parameters? If you use the Class Wizard you'll get code a lot like this:

Public Function Add(ColumnName As String, ColumnLength As Integer, AllowNulls As Boolean, DataType As String, Columns As Columns, Optional sKey As String) As Column

Dim objNewMember As Column

'create a new object
Set objNewMember = New Column

'set the properties passed into the method
If IsObject(ColumnName) Then
Set objNewMember.ColumnName = ColumnName
objNewMember.ColumnName = ColumnName
End If

With objNewMember
.ColumnLength = ColumnLength
.AllowNulls = AllowNulls
.DataType = DataType
End With

Set objNewMember.Columns = Columns
If Len(sKey) = 0 Then
mCol.Add objNewMember
mCol.Add objNewMember, sKey
End If

'return the object created
Set Add = objNewMember
Set objNewMember = Nothing

End Function

No magic, we're just creating the object "inside" the add method and then adding it to the columns collection. If you wanted to pass an object, it might look like this instead:

Public Function Add(oColumn As Column)

mCol.Add oColumn

End Function

Now that's a very quick intro to objects in VB. Probably too quick! The thing I hope you'll take away from this is that it doesn't matter if you're adding a column to a table, a login to a server, or a user to a database - each time you'll dim and create the object, set whichever properties you need, then pass that object into the appropriate add method.

And finally, here is the sample code for adding a varchar column:

Dim oServer As SQLDMO.SQLServer
Dim oTable As SQLDMO.Table
Dim oColumn As SQLDMO.Column

'this logs into the local server using NT auth
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
End With

'we don't need a reference to the database, we can access the table directly
Set oTable = oServer.Databases("Pubs").Tables("Categories")

'create a column object with the appropriate properties. For data types that have a
'user defined length you have to set the length property too. For this example Im
'allowing nulls since the table should already have data and not allowing nulls
'would cause the alter to fail!
Set oColumn = New SQLDMO.Column
With oColumn
.Datatype = "varchar"
.Length = 500
.AllowNulls = True
End With

'apply the change
With oTable
.Columns.Add oColumn
End With

'clean up
Set oServer = Nothing

As always I'd enjoy receiving comments and questions about the article -- please click the 'Your Opinion' tab below to enter your thoughts.

Total article views: 4708 | Views in the last 30 days: 2
Related Articles

Compress All Objects

Creates compression scripts for all objects in all databases in the instance.


Object Search

Procedure to search any database (or a combination of databases, including all) for a specific strin...


ActiveX component can't create object - SQL2005 Agent Job

ActiveX component can't create object - SQL2005 Agent Job


constraints on columns while creating the table

constraints on columns while creating the table


Conversion of default objects to column defaults

This script can convert the usage of bound default objects in tables to column defaults

sql server 7    
visual basic 6