Custom/arbitrary database metadata

Question

I think that the ability of some reflective managed environments (e.g. .NET) to add custom metadata to code entities in the form of attributes is very powerful. Is there any mechanism for doing something similar for databases?

Databases obviously already have a fair amount of metadata available; for example, you can get a list of all tables, columns, and foreign key references, which is enough to put together a schema diagram. However, I could imagine plenty of uses for something more generic, such as something along the lines of this imaginary fusion of C# and DDL:

[Obsolete("Being replaced by the ClientInteraction table")]
create table CustomerOrder (

    [SurrogateKey]
    MyTableId int identity(1,1) primary key

    [NaturalKey]
    [ForeignKey("Customer", "CustomerId")] /* Actual constraint left out for speed */
   ,CustomerId int not null

    [NaturalKey]
    [ConsiderAsNull(0)]
    [ConsiderAsNull(-1)]
   ,OrderId int not null

    [Conditional("DEBUG")]
   ,InsertDateTime datetime
)

The example is a little contrived but hopefully makes my question clearer. I think that the ability to reflect over this kind of metadata could make many tasks much more easily automated. Is there anything like this out there? I'm working with SQL Server but if there's something for another DBMS then I'd still be interested in hearing about it.

Answer 1

You can pull out almost anything to do with an object out of SQL Server, if you know where to look. If you need to supply more "attributes" you are extending the problem domain with more "concepts" and this goes against the KISS principle -- also a relational database is obviously perfect capable of representing any relations to do with your data and the example you have shown. I cannot think of a reason why you would want to add user supplied meta-data to a table.

If however, you have the need, stick to adding the additional attributes in an ORM. Or create a separate table that describes the meta-data for your specific problem. You also have the ability to link in Table Defined Functions which you are free to write in C# -- you can therefore represent your attributes in C# -- again: I think this to be overkill.

Answer 2

in SQL Server 2005 and up you can use sp_addextendedproperty and fn_listextendedproperty (as well as the SSMS gui) to set and view descriptions on various database items. here is an example of how to set and view a description on a table column:

--code generated by SSMS to set a description on a table column
DECLARE @v sql_variant 
SET @v = N'your description text goes here'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'your_schema_name_here', N'TABLE', N'your_table_name_here', N'COLUMN', N'your_column_name_here'


--code to list descriptions for all columns in a table
SELECT * from fn_listextendedproperty (NULL, 'schema', 'your_schema_name_here', 'table', 'your_table_name_here', 'column', default);