database table / column metadata

  • Thread starter Thread starter Daniel M
  • Start date Start date
D

Daniel M

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
 
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
 
Think I've answered my own question ...

It looks like the best way to centralise table and column metadata across a
system would be to use SQL Server's built-in extended properties. This seems
to be an MS recommended way for storing metadata such as captions, input
masks etc:

http://msdn.microsoft.com/msdnmag/issues/0800/sql2000/default.aspx

I'm planning to create queries that extract this data from extended
properties and export periodically to tables / XML file for quicker lookup.

Any other comments welcome.
 
Back
Top