SMO:Working with Extended Properties

One of the nice things about Server Management Objects(SMO) is how the modeling of the classes folows almost exactly how items are aligned in the database structure.

Sadly, one of the least used items in a database it that extended properties that are on almost every level(database, table, column) of the SQL Server database architecture. However, these objects allow you to tag the database objects with your own metadata thus allowing to to almost infinitely extend the amount of information held in that particular object. Forget about the data ….we are talking about how to tell what the data means or is used for. I like to use the extended properties so that the database becomes a self documenting entity. 

You can make the process easier by using SMO. Check my earlier post and presentations about the usage of SMO if you are unsure. Anywho, each major database layer will have an ExtendedProperties collection on it. So….I can write some code to traverse a list of extended properties(EPList) …if the name does not exist in the list of extended properties for the database then it is added with the default value.

For Each EPName As String In EPList

If Not db.ExtendedProperties.Contains(EPName) Then

Dim ep As New ExtendedProperty(db, dbext, “Default Value”)

ep.Create()

End If

Next

So all you need to do to add an extended property to an object is to have a reference to that object, the name you want the property called, and a value. I could have just as easily written this to the table level by using instead

Dim ep As New ExtendedProperty(myTableRefence, dbext, “Default Value”)

ep.Create()

Hopefully, this gives you some good ideas to use to follow through with your own projects.

Cheers,
AJ