Thanks, Rick. Don't know yet if I'll need extended properties or comments but this will be useful if I do.
>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?
Regards. Al
"Violence is the last refuge of the incompetent." -- Isaac Asimov
"Never let your sense of morals prevent you from doing what is right." -- Isaac Asimov
Neither a despot, nor a doormat, be
Every app wants to be a database app when it grows up