How To Retrieve Table Index Information from SQL Server (195534)

MORE INFORMATION

   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

1. Create a program file called Sp_index.prg using the following code:
   
   Note The user, Username, 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
   							

2. In the Command window type the following command: do sp_index