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.
View products that this article applies to.
Column Name Data Type Description
------------------------------------------------------------------------
TABLE_QUALIFIER varchar(32) The name of the table qualifier. This
column represents the database name, and it
might be NULL.
TABLE_OWNER varchar(32) The name of the table owner. This column
represents the name of the database user
who created the table and it always returns
a value.
TABLE_NAME varchar(32) The name of the table. This column
represents the table name as listed in the
sysobjects table, and it always returns a
value.
COLUMN_NAME varchar(32) The name of the column, for each column
of the TABLE_NAME returned. This column
represents the column name as listed in the
syscolumns table, and it always returns a
value.
When a primary key involves multiple
columns, sp_pkeys returns one row for each
column used in the primary key expression.
KEY_SEQ smallint The sequence number of the column in a
multi-column primary key, and it always
returns a value.
PK_NAME varchar(32) A primary key identifier, and it is NULL if
it is 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 (Table_Name C(128), Column_Name C(128), ;
Key_Seq I, 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_pkeys " + 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
SCAN
IF RECNO()=1
* Insert a new record into the SQLKeys cursor.
INSERT INTO SQLKeys ;
VALUES ;
(SYSKeys.Table_Name, SYSKeys.Column_Name, ;
SYSKeys.Key_Seq,SYSKeys.PK_Name)
ELSE
* Multicolumn key.
* Concatenate to get the expression.
REPLACE SQLKeys.Column_Name WITH ;
ALLTRIM(SQLKeys.Column_Name) + ;
"+" + ALLTRIM(SYSKeys.Column_Name)
ENDIF
ENDSCAN
ENDIF
SELECT SQLResult
SKIP
ENDDO
=SQLDISCONN(gnConnHandle)
ENDIF
ENDIF
SELECT SQLKeys
BROW LAST
CLOSE ALL
RETURN
Keywords: kbnosurvey, kbarchive, kbclientserver, kbdatabase, kbhowto, kbsqlprog, KB195526