How To Retrieve Foreign Key Information from SQL Server (196084)

MORE INFORMATION

   Column Name          Description
   -----------------------------------------------------------------------

   PKTABLE_QUALIFIER    Name of the table (with the primary key) qualifier.
                        This column represents the database name for the
                        table with a PRIMARY KEY constraint and may be
                        NULL.


   PKTABLE_OWNER        Name of the table (with the primary key) owner.
                        This column represents the name of the database
                        user that created the table (with a PRIMARY KEY
                        constraint) and always returns a value.

   PKTABLE_NAME         Name of the table (with the primary key). This
                        column represents the table name (with a PRIMARY
                        KEY constraint) as listed in the sysobjects table
                        and always returns a value.

   PKCOLUMN_NAME        Name of the primary key column(s), for each column
                        of the TABLE_NAME returned. This column represents
                        the column name as listed in the syscolumns table
                        and always returns a value.

   FKTABLE_QUALIFIER    Name of the table (with a foreign key) qualifier.
                        This column represents the database name for the
                        table (with a FOREIGN KEY constraint) and may be
                        NULL.

   FKTABLE_OWNER        Name of the table (with a foreign key) owner. This
                        column represents the name of the database user
                        that created the table (with a FOREIGN KEY
                        constraint) and always returns a value.

   FKTABLE_NAME         Name of the table (with a foreign key). This column
                        represents the table name as listed in the
                        sysobjects table (with a FOREIGN KEY constraint)
                        and always returns a value.

   FKCOLUMN_NAME        Name of the foreign key column(s), for each column
                        of the TABLE_NAME returned. This column represents
                        the column name as listed in the syscolumns table
                        and always returns a value.

   KEY_SEQ              Sequence number of the column in a multicolumn
                        primary key. This field always returns a value.

   UPDATE_RULE          Action applied to the foreign key when the SQL
                        operation is UPDATE. SQL Server returns 1 for this
                        column.

   DELETE_RULE          Action applied to the foreign key when the SQL
                        operation is DELETE. SQL Server returns 1 for this
                        column.

   FK_NAME              Foreign key identifier. This is the FOREIGN KEY
                        constraint name, and may be NULL if not applicable
                        to the data source.

   PK_NAME              Primary key identifier. This is the PRIMARY KEY
                        constraint name, and may be NULL if not applicable
                        to the data source.
				

   * Substitute the server name.
   #DEFINE Connect_String 'DRIVER={SQL Server};SERVER=MY_SERVER;' + ;
      'DATABASE=PUBS;UID=<user name>;PWD=<strong password>'
   * Create a cursor to store information.
   CREATE CURSOR SQLKeys (Parent_Qualifier c(128), Parent_Owner c(128), ;
      Parent_Table_Name c(128), Parent_Column_Name c(128), ;
      Foreign_Qualifier c(128), Foreign_Owner c(128), ;
      Foreign_Table_Name c(128), Foreign_Column_Name c(128), ;
      Key_Seq I, FK_NAME c(128), PK_Name 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_fkeys " + ALLTRIM(Table_Name)
            * Execute the stored procedure and return data to a cursor.
            =SQLEXEC(gnConnHandle,SQLCommand,'syskeys')
            * Select the cursor.
            SELECT SYSKeys
            IF RECCOUNT()>0
               SELECT SQLKeys
               SQLKEY_Exists=.F.
               SCAN FOR Parent_Table_Name=SYSKeys.PKTable_Name ;
                     AND Foreign_Table_Name=SYSKeys.FKTable_Name
                  * Multicolumn key.
                  * Concatenate to get the expression.
                  REPLACE SQLKeys.Foreign_Column_Name WITH ;
                     ALLTRIM(SQLKeys.Foreign_Column_Name) + ;
                     "+"  + ALLTRIM(SYSKeys.FKColumn_Name)
                  SQLKEY_Exists=.T.
               ENDSCAN
               IF !SQLKEY_Exists
                  * Insert a new record into the SQLKeys cursor.
                  INSERT INTO SQLKeys ;
                     VALUES ;
                     (SYSKeys.PKTable_Qualifier, SYSKeys.PKTable_Owner, ;
                     SYSKeys.PKTable_Name, SYSKeys.PKColumn_Name, ;
                     SYSKeys.FKTable_Qualifier, SYSKeys.FKTable_Owner, ;
                     SYSKeys.FKTable_Name,SYSKeys.FKColumn_Name, ;
                     SYSKeys.Key_Seq,SYSKeys.FK_NAME,SYSKeys.PK_Name)
               ENDIF
            ENDIF
            SELECT SQLResult
            SKIP
         ENDDO
         =SQLDISCONN(gnConnHandle)
      ENDIF
   ENDIF
   SELECT SQLKeys
   BROW LAST
   CLOSE ALL
   RETURN
					

   DO SP_FKEYS