By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,977 Members | 1,369 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,977 IT Pros & Developers. It's quick & easy.

database table / column metadata

P: n/a
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

Nov 18 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 18 '05 #2

P: n/a
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/is...0/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.
"Daniel M" <da************@becta.org.uk> wrote in message
news:#D**************@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

Nov 18 '05 #3

P: n/a
Thanks Brian

As you'll see from my next post, I came to a similar conclusion.

"Brian Moran" <br***@solidqualitylearning.com> wrote in message
news:ug**************@TK2MSFTNGP09.phx.gbl...
Of course you could roll your own...

You should also review this in BOL:

Property Management
Microsoft® SQL ServerT 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


Nov 18 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.