I added this:

select SO.name as 'table', SC.name as field,

ST.name as datatype, SC.length as 'size',

COALESCE(sp.value,df.Comment,'') as 'description'

from syscolumns SC

inner join sysobjects SO on SC.id = SO.id

inner join systypes ST on ST.xusertype = SC.xusertype

left join sysproperties sp on sp.id = so.id and sp.smallid = SC.colid and sp.name = 'MS_Description'

left outer join doc_fields df on df.tablename = so.name and df.fieldname = SC.name

where SO.xtype = 'U' and SO.status > 0

order by SO.name, SC.colorder

Now it looks into the extended properties or my table.

I keep a corresponding dictionary database for every database I design with descriptions of the tables, views and columns.  Good idea to add stored procs, I hadn't thought of that.  I'm required to document data classification (you know what's sensitive and what's not), so that's in there too.  For that last database I developed, all the columns are populated from a non-windows system so I added the source information too.  Now I can talk to the admins on that system in their terms if I need to.

• It is not easy to update
• It is not intrinsically linked to the database so can be obsolete fast
• Does not automatically cross reference to bookmarks within itself
• Isn't a natural reference format (such as CHM or web ) for programmer
• Is very bulky

For those of you who want to use the extended properties and think that there is no extended properties for the table in general, there is a way to enter it directly from Enterprise Manager.

When you are in Design Table, right click one of the columns and chose Properties.  The field called Description on the Tables tab is stored in the extended properties.

You can access it from a script with:

SELECT @tabledesc = value
 FROM   ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', @tblname, DEFAULT, DEFAULT)

I have a script called usp_Table_Doc that takes the table name as a parameter outputs a file layout, complete with the extended properties as descriptions, if anyone is interested.

Steve