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

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);