Microsoft KB Archive/196084

= How To Retrieve Foreign Key Information from SQL Server =

Article ID: 196084

Article Last Modified on 3/2/2005

-

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
 * Microsoft Data Access Components 2.5

-



This article was previously published under Q196084



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.



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= and PWD= 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= ;PWD= ' * 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

