In Help Builder (which documents SQL schemas from a database - http://www.west-wind.com/wwhelp) I use:

To get tables and views:

IF (this.oSql.Execute("sp_help") = -1)
   this.cErrormsg = this.oSql.cErrorMsg
   RETURN .f.
ENDIF

IF llViews
   SELECT * from TSQLQuery WHERE object_type = "view" AND owner # "sys" AND owner # "INFORMATION_SCHEMA" INTO CURSOR TTables
else
   SELECT * from TSQLQuery WHERE object_type = "user table" INTO CURSOR TTables
ENDIF

To get table comments:

   *** Get Description
   lcSql = "SELECT   * FROM ::fn_listextendedproperty('MS_Description', 'user', 'dbo','table', '" + ;
                    lcTable + "', NULL,default)"
   
   lnResult = this.oSQL.Execute(lcSQL,"TTableComment")

To get columns:

*** Retrieve the standard Column stats
lnResult = THIS.oSQL.Execute("Exec sp_columns '" + lcTable + "'")

To retrieve column comments:

*** Retrieve extended properties in order to retrieve column comments 
lcSql = "SELECT   * FROM ::fn_listextendedproperty('MS_Description', 'user', 'dbo','table', '" + ;
                    lcTable + "', 'column',default)"
lnResult = this.oSQL.Execute(lcSQL,"TTableComments")

Works rather well and I think for 2000-2008 but definitely for 2005 and 2008.

+++ Rick ---

.
>I'm creating a utility that will be used to periodically synchronize a VFP database with a SQL Server 2005 Express database (SugarCRM). As part of that I'm building a cross-reference of tables and columns between the two databases.
>
>In VFP9, I can connect to the SugarCRM database as sa so I can do anything I want. I believe there are system stored procedure(s) that can be used to extract schema information such as:
>
>- list of tables in the database
>- list of columns of a given table
>- values of various attributes of a column
>
>There seem to be literally hundreds of system stored procedures. Can someone recommend one(s) that could help me get the schema information I need?