Database Table, Column & Index Info

[dgrewe]

I have to create a Database clone application (in VFP) to copy a database from one MSSQL/Oracle server and keep it mirrored to another MSSQL/Oracle server, (Yes I know there is a built-in Oracle / Mssql application) but do to the costs and licensing fees the clients of this application want me to write one.

I can get everything I need about the Stock database tables from the applications' VFP database but that does not tell me anything about the user created tables Unique to each site. I can handle the Oracle side of the problem but I need help with the MSSQL 2005 side.

I know I can do a SELECT NAME as TABLENAME FROM SYS.TABLES where object_id > 1000 (the 1000 is a guess) to get the name of every table and columncount in the MSSQL database and I and can join the Sys.Indexes & Sys.Tables table on the object_id column.

What I do not know how to get is:
1. The Primary Key Field(s) name on the Table,
2. The Primary Key Index name on the Table.

I need to populate a VFP table named DBFCONTROL columns with the following info on every table in the database
TABLENAME C(25) Name of the table
COLUMNCOUNT N(3,0) The number if columns in the Table.
PRIKEYFIELD C(25) Name of the Field(s) the index is on
PRIKEYINDEX C(25) Name of the Index
(remainder of fields populated at run time)

Anyone have a few minutes to help me out before I have to spend Hours in research ?


David W. Grewe Dave