|
 |
 |
 |
|
Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms
of Use |
Trademarks
Article ID: 196084 - Last Review: March 2, 2005 - Revision: 3.3 How To Retrieve Foreign Key Information from SQL ServerThis article was previously published under Q196084 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. 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:
Transact - SQL Help; search on: "sp_fkeys" (c)
Microsoft Corporation 1998, All Rights Reserved. Contributions by John Desch,
Microsoft Corporation.
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
| kbclient kbclientserver kbcode kbdatabase kbhowto kbsqlprog KB196084 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
|
|
 |
|
 |
|
|
|
| |