Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How To Retrieve Foreign Key Information from SQL Server


View products that this article applies to.

This article was previously published under Q196084

↑ Back to the top


Summary

When creating remote views using SQL Server tables as the data source, Visual FoxPro automatically sets the KeyField property to .T. for columns that are included in a primary index key. You can use the SQL Server stored procedure, sp_pkeys, to return primary key information from SQL Server. The sp_pkeys stored procedure, however, does not return information regarding the relationship(s) between tables.

In some circumstances, a developer may want to retrieve the foreign key information programmatically to provide greater detail of the relationship between tables. This article shows how to use the stored procedure sp_fkeys to return foreign key information from SQL Server.

↑ Back to the top


More information

The sp_fkeys stored procedure returns a cursor with logical foreign key information for the current environment. This procedure shows foreign key relationships including disabled foreign keys. The sp_fkeys stored procedure is equivalent to SQLForeignKeys in ODBC. The results returned are ordered by FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, and KEY_SEQ.

The columns returned by sp_fkeys follow:
   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.
				
Create a program named SP_fkeys.prg using the following code:

NoteYou must change UID=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.
   * 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
					
In the Command window enter and run the following code:
   DO SP_FKEYS
					

↑ Back to the top


References

Transact - SQL Help; search on: "sp_fkeys"

(c) Microsoft Corporation 1998, All Rights Reserved. Contributions by John Desch, Microsoft Corporation.

↑ Back to the top


Keywords: kbclient, kbclientserver, kbcode, kbdatabase, kbhowto, kbsqlprog, KB196084

↑ Back to the top

Article Info
Article ID : 196084
Revision : 6
Created on : 3/2/2005
Published on : 3/2/2005
Exists online : False
Views : 513