You
develop an application that creates various database objects
like tables, views etc. You are also required to document
the tables and their associated columns so that you can
produce a data dictionary when needed. What are the options
you have to store these details?
You develop
an database schema that is going to be used by various
applications in your organization. In the database schema,
you have tables that capture various peices of information
(like address, phone number, email addresses etc). Fields
like phone numbers have a mask that is to be used by all
the applications uniformly so that the data storage and
display is consistent. What are the options you have to
store information like field masks?
In the first case, you can
use third party tools that can store these information and
retrieve them on demand to produce reports. You can also design
your own meta-data schema to store this information. In the
second case, you need to either store such information in
a database table, or in a global configuration file (like
an XML file) that is then used by each application.
In both these situations, there is a need to depend on seperate
methods or tools or methods to implement the solution that
we want. It is in situations like these that SQL Server comes
to our rescue. SQL Server 2000 introduced the concept of "extended
properties" that users can define on various database
objects. Extended properties can be used to store application-specific
or site-specific information about the database objects. Because
the property is stored in the database, all applications reading
the property can evaluate the object in the same way. This
helps enforce consistency in the way data is treated by all
of the programs in the system.
For the purpose of specifying extended properties, the objects
in a SQL Server 2000 database are classified into three levels
(0, 1, 2). Level 0 is the highest level and 2 is the lowest
level. The table lists the level-0 objects, user and user-defined
data type, with their valid level-1 and level-2 objects.
Level
0
Level
1
Level
2
User
Table
Column,
index, constraint, trigger
View
Column,
INSTEAD OF trigger
Schema-bound
View
Column,
index, INSTEAD OF trigger
Stored
Procedure
Parameter
Rule
<None>
Default
<None>
Function
Column,
parameter, constraint
Schema-bound
Function
Column,
parameter, constraint
User-defined
data type
<None>
<None>
Extended properties provide
only a named location in which to store data. All applications
must be coded to query the property and take appropriate action.
For example, adding a caption property to a column does not
create a caption that can be displayed by an application. Each
application must be coded to read the caption and display it
properly.
Extended Properties
Stored Procedures
Extended properties are managed by using the following stored
procedures in SQL Server.
sp_addextendedproperty
sp_updateextendedproperty
sp_dropextendedproperty
Managing Properties
You use the "sp_addextendedproperty"
to add an extended property to a datbase object. If the property
already exists, the call fails. For example, let's say we
want to add a "Description" property to the "authors"
table. Here is how you do it.
sp_addextendedproperty 'Description', 'This table is used
to store the various authors and their details', 'user', 'dbo',
'table', 'authors', null, null
The parameters of the command
are as follows:
The
first parameter is the name of the property. Here, it
is called Description.
The second
parameter is the value for the property.
The third
and fourth parameters identify the user.
The fifth parameter
identifies the type of object for which this property
is being added.
The sixth parameter
identifies the name of the object.
The seventh
parameter identifies the sub-level of the object identified
by the fifth parameter (for example a column of a table).
The eight parameter
identifies the name of the sub-level object (for example
a column name).
As another example, consider
that you want to add a "Description" property to
the "au_id" column of the "authors" table.
Here is how you do it:
sp_addextendedproperty 'Description', 'This column is used
to store the author id', 'user', 'dbo', 'table', 'authors',
'column', 'au_id'
Now that we have added two
properties, how do we display the values to an end-user??
SQL Server provides a function called "fn_listextendedproperty"
that uses the various levels mentioned above to display the
properties stored at different levels. For example, if we
want to display the value of the "Description" property
added at the table and the column level, the following commands
can be used:
The first command displays the
property defined at the table level for the "authors"
table and the second command displays the properties defined
at the column level for the "authors"
table. By specifying null in the last parameter of the second
command, we get all the column level "Description"
properties. We can also fetch a particular column's property
by specifying the column name as the last parameter like:
Till now, we have seen the addition
of only one property (called "Description") to a column.
Let's take another example, where we want to add a "Description"
property to the "phone" column of
the authors table and also a "Display Mask"
property to the same column. Here is how you do it:
sp_addextendedproperty 'Description', 'This column is used
to store the phone number for the author', 'user', 'dbo',
'table', 'authors', 'column', 'phone' sp_addextendedproperty
'Display Mask', '(999) 999-9999', 'user', 'dbo', 'table',
'authors', 'column', 'phone'
Now that we have added both
the properties, to display the properties attached to the "phone"
column, you can use the following command:
Note the
usage of "null" as the first parameter
to display all the properties for the "phone" column.
One important thing to remember is that we have seen only
how to create properties and display them. To actually use
them in your application, the application has to query the
property and then use it appropriately. Once a property has
been defined, you can use the "sp_updateextendedproperty"
procedure to update the values if need be. For example, if
we want to change the "Description"
property for the "authors" table,
here is how you can do it:
Finally, if you want to drop
an extended property, you use the "sp_dropextendedproperty"
procedure. For example, if you want to drop the "Display
Mask" property from the "phone" column, here
is how you can do it:
Using the concepts learnt till now, let's develop a small web-page
that shows the tables in the pubs database
and the Description property associated with
them. Instead of using a sophisticated tool to do this task,
I'm going to use the sp_makewebtask command.
You can learn more about this command and its usages by reading
my article SQL
Server and HTML. Here is the command to create
a HTML page.
sp_makewebtask
@outputfile
='C:\Temp\Pubs.html',
@query = 'SELECT
objname AS [Object Name], value AS Description FROM ::fn_listextendedproperty
(''Description'', ''user'', ''dbo'', ''table'',
null, null, null)',
@resultsTitle
= 'Pubs Data Dictionary',
@dbname =
'pubs'
The output of the command will
look like:
You can put lots of others features
and cosmetic changes on this page by using the techniques described
in the mentioned article. Have fun!!
Conclusion
In this article, we have seen how to define properties against
database objects (like tables) and how to retrieve, update and
delete them. The applications of such a feature in SQL Server
2000 is numerous. For example, we can easily write data dictionary
applications that manipulate these properties and then display
them. By defining properties at the database level, applications
can pick information from a centralized store and use them appropriately.
