SQL Server - Table Metadata

Question

I have a 1000 tables in the SQL Server Database.

If I wanted to add a brief description of what each table does in my application, should I be creating another table to maintain that information? Or is there a better way to this?

For example ; If there is a table [PurchaseOrdersConcrete] and I want to note that - This table stores all POs from Fortune500 companies.

Should I create another meta-data table for this purpose, or is there a - Notes - column somwhere in SQL Server already ... (say in sys.tables)?

Answer 1

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, Value
FROM   ::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.