Of course you could roll your own...
You should also review this in BOL:
Property Management
Microsoft® SQL Server™ 2000 introduces extended properties that users can
define on various objects in a database. These 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 how data is treated by all of the programs
in the system.
Each extended property has a user-defined name and value. The value of an
extended property is a sql_variant that can contain up to 7500 bytes of
data. Individual database objects can have multiple extended properties.
Extended properties are managed using three system stored procedures:
sp_addextendedproperty, sp_updateextendedproperty, and
sp_dropextendedproperty. You can read the value of an existing extended
property using the system function FN_LISTEXTENDEDPROPERTY.
There is no convention or standard for defining extended properties. The
database designer sets the rules specifying the property names and contents
when the database is designed, and then the applications accessing the
database have to be coded to follow those rules or conventions.
See Also
Using Extended Properties on Database Objects
fn_listextendedproperty
sp_addextendedproperty
sp_dropextendedproperty
sp_updateextendedproperty
©1988-2000 Microsoft Corporation. All Rights Reserved.
a quick google on those procs and functions will show you a number of people
who have built reasonably helpful sample apps for managing these properties.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Daniel M" <da************@becta.org.uk> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
I'm building a medium-scale data-entry web application, which involves
creating data entry forms, record listings and detail screens for lots of
database tables.
Rather than designing a series of similar web pages for each table I'm
looking into recording metadata about tables / columns in the database and
using this to determine presentation.
Let's keep things simple for now and assume that I'm interested in adding
more user friendly captions to replace actual column names, e.g. 'Unit
Price' instead of 'unit_price' and would also like column descriptions to
be easily available. If this data were held in the database, then captions
and descriptions could be looked up dynamically within the application. This
would be better than defining captions individually within a .Net DataGrid
or HTML template. It would also allow metadata to be shared between
applications.
A brief search around dev sites hasn't revealed anything. Are there any
recommended ways of doing this?
This actually looks pretty easy, the data could be held in 2 tables:
TABLE: table_metadata:
- table_id
- db_table_name (name of table in database)
- caption (user friendly title for table)
- description
TABLE: column_metadata
- id
- table_id
- db_column_name (name of table in database)
- caption (user friendly column title)
- description
Thanks in advance
Dan
