Web Listing 1 creates the sample database for this article. (To download this article's Web listings, enter InstantDoc ID 26273 at http://www.sqlmag.com and click Download the code.) Web Listing 2 creates the usp_CreateExtendedProperties stored procedure, which appends extended properties to each table and column of the database that it is executed in, standardizes the extended-property names that are in use, and creates a business metadata repository. You can use this modified version of Pfeiff's code to create your own business metadata repository for the database that you created in Web Listing 1.
The code in Web Listing 2 retrieves a list of all user table names and column names from the user tables, then attaches to each user table and each user column the following 11 extended properties:
- CreationDate: the date the database object was added
- CreatedBy: the name of the person who added the database object
- MS_Description: the Description property from Enterprise Manager's Table Design window
- UsedFor: the main reason the data value is in the database
- DataSource: the origin of the data value
- SourceApplication: the name of the program that captured the data
- ProcNeeded4Storage: the name of the program that processes the data after capture but before storage
- StillActive: the database object's activity status
- ReplacedBy: the name of the database object that replaced an inactive database object noted by StillActive
- EnglishLabel and FrenchLabel: examples of how to store field labels in multiple languages
When you execute Web Listing 2's stored procedure, all the tables and columns in the database acquire the 11 extended properties. To see the extended properties, you need to open Query Analyzer and expand the tree until you can see the database that you've modified. Then, just place the cursor over a table or column name, right-click, and choose the Extended Properties option. Figure 2 shows the extended properties for the EMPLOYEE table.
You can add values to the extended properties in three ways. You can use Figure 2's GUI display, you can access the extended properties through Visual Basic (VB) code (your developers will probably use this method), or you can use the code that Listing 1 shows to make global changes to the extended-property values.
Note that Listing 1 inserts a data value (the first argument) into every occurrence of the extended property (the second argument) in the database. And the stored procedure that the listing creates overwrites a preexisting extended-property value. Listing 2 shows two examples of how you can use the fn_listextendedproperty() function to view the extended-property values you created. Listing 2's third example, a code statement from BOL, contains a bug and doesn't work.
Metadata for All
Business metadata offers a vital resource to everyone in the office, but simply storing the metadata in the database isn't a sufficient business solution. You need to develop a scheme to distribute the metadata to all your users, technical and nontechnical alike. You should also document your transactional database metadata as frequently as you perform backups and performance-tuning checks. If you follow this easy plan, the next time you're asked to create a report for one of your company's project managers, you'll know that you should use the data from the PMBPMSRC table.
End of Article
Prev. page
1
[2]
next page -->
