Microsoft KB Archive/195534

= How To Retrieve Table Index Information from SQL Server =

Article ID: 195534

Article Last Modified on 7/1/2004

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 3.0b for Macintosh

-



This article was previously published under Q195534



SUMMARY
When accessing SQL Server tables, a developer may want to retrieve information regarding index keys. This article shows how to use the stored procedure sp_helpindex to return table index information from SQL Server.



MORE INFORMATION
The sp_helpindex stored procedure returns a cursor with index information for a table.

The sp_helpindex returns the following columns:   Column name          Description. INDEX_NAME          Name of the index. INDEX_DESCRIPTION   Description of the index. INDEX_KEYS          Column(s) that comprise the index expression.

Step-by-Step Example
  Create a program file called Sp_index.prg using the following code:

Note The user,, must have permissions to perform these operations on the database. * Substitute the server name. #DEFINE Connect_String 'DRIVER={SQL Server};SERVER=MY_SERVER;' + ; 'DATABASE=PUBS;UID=UserName;PWD=StrongPassword' * Create a cursor to contain the information to return. CREATE CURSOR SYS_Indexes (Table_Name c(128), Index_Name c(128), ;        Index_Description c(128), Index_Keys c(128)) * Connect to SQL Server. gnConnHandle=SQLSTRINGCONN(Connect_String) IF gnConnHandle>0 * Get the tables available on SQL Server. SQLConnTables=SQLTABLES(gnConnHandle) IF SQLConnTables>0 SELECT SQLResult INCnt=0 DO WHILE !EOF * Create a command to execute the stored procedure. SQLCommand="sp_helpindex " + ALLTRIM(Table_Name) * Execute the stored procedure and return data to a cursor. =SQLEXEC(gnConnHandle,SQLCommand,'tmp_sys_data') * Select the cursor. SELECT tmp_sys_data INSERT INTO SYS_Indexes ; VALUES ; (SQLResult.table_name, tmp_sys_data.Index_Name, ;                 tmp_sys_data.Index_Description, ;                  tmp_sys_data.Index_Keys) SELECT SQLResult SKIP ENDDO =SQLDISCONN(gnConnHandle) ENDIF ENDIF SELECT SYS_Indexes BROW LAST CLOSE ALL RETURN  In the Command window type the following command: do sp_index 

