SQL Server - Table Metadata

You do that via Extended Properties. An msdn overview of the usage of them can be found here. I have used them for the exact purpose you describe above.

The easiest way to manage them is directly within SSMS by right clicking on your object and selecting properties. But you can also work with them from within your application via tsql.

To add new extended properties use sp_addextendedproperty.

To retrieve existing extended properties a typical approach is to query ::fn_listextendedproperty as this example below shows.

SELECT   objType, objName, Type, ValueFROM   ::fn_listextendedproperty (null, 'user', 'dbo', 'table','<your table>', null, null)

Update and delete operations on them are made possible through use of sp_updateextendedproperty and sp_dropextendedproperty

Further, SSMS makes use of them for some of it's own metadata. A way to see them in action is take a look at one of your views in SSMS. Right click on it and select properties. Then click on 'extended properties'. You'll likely see an entry that says something about MS_DiagramPane???. This is where MS stores the layout of your view so that every time you open the view in design mode it looks the same way you left it last time.

These have been available as far back as SQL2000 but have gained widespread use more recently.

As RTHomas suggested, use the extended properties, even better use RedGates SQLDoc tool which will make it easy for you to populate and maintain those fields:

http://www.red-gate.com/products/sql-development/sql-doc/

It even produces printed and/or web documentation for you when you are done. Great tool.