Use Extended Properties to Synchronize Your Documentation, Database Objects, and Code : Page 3

Fill in the Blanks of Your Worksheet
Once you have sweated out the connections between what the state is asking for and where you think you can get it, you are prepared to fill in the blanks. Your resulting Excel product will look something like Figure 1, which shows the "money columns" for changes to the schema above.ConcatMedsForLookup, a calculated column from the state's specification. Implementation table is the table it will report from, and mapped from is the source of the data.

Figure 1. Excel Product After Mapping Is Complete

Once the Excel worksheet has the facts recorded in it, you can use it to create a string of the form required to create an extended property. To create an extended property for the meaning of an attribute, you could run the following statement in Query Analyzer:

sp_addextendedproperty
	"'Meaning',"
	"A=Adjusting, V = Voiding a previously submitted record."
	" 'user',"	" dbo,"
	" 'table',"	CommonDetail
	" 'column,'" 	RecordType

Make sure that you are referencing things you have in your schema before trying this, otherwise an error will complain about its non-existence. Alternatively, you could add the extended property by right-clicking the object and using the interface provided. Figure 2 illustrates the interface, which is a convenient way to enter a property or two, however, if you have a lot of properties to add or edit, the script method is better.

Figure 2. Singleton Extended Property Entry

You can retrieve the properties you create by using select * from fn::ListExtendedProperty(default, default, default, default, default, default, default). Next time, I will talk about how you can animate other processes by using the strings stored in the extended properties.

Since the above approach binds the documentation to the schema, how do I keep track of the chain between what was reported and the assumptions underlying the report in ad-hoc report situations? Look back at Figure 1 and you'll notice a mapping to something called Calculated rather than a table and column combination, and a cell named CODE (in boldface) in place of a column name. The purpose of this artifact is to store the SQL necessary to fill that requirement. In other words, I copy the query text out of QA and insert it into a comment in Excel. As a result, I don't have to remember which combination of stored procedures and/or DTS or other interventions I needed to create that result. I can just store the code with the report.

In Figure 3, the ClaimEncounterIndicator for the Dental Segment is calculated with the SQL in the comment field.

Figure 3. SQL That Calculates the ClaimEncounterIndicator for the Dental Segment

I applied the above technique to document our fulfillment of the state's definition of this attribute, but you can use the SQL in the comment field to document a report result, which is what I do with it 99 percent of the time.

Little Islands of Intervention
The good thing about extended properties is that because they are part of the database object they are easy to keep with the database object. The bad thing is that because they have no interface someone has to write the statements to add and edit the properties. As I stated earlier, you can get Excel to do most of the work.

I hope this discussion has given you ideas about how to leverage extended properties. These little "islands of intervention" have plenty of uses beyond documentation or mapping. Next time, I'll look into storing executable statements in extended properties and retrieving them in ActiveX scripts in DTS.

Previous Page