dbo.fSQLPrimaryKeys
This function returns as a result set the column names that make up the primary key for a table. Dbo.fSQLPrimaryKeys does not support returning primary keys from multiple tables in a single call.
Syntax
dbo.fSQLPrimaryKeys (<'
pkey_table_qualifier
' | null>, <'
table_name
' | null>)
Arguments
Table 114
dbo.fSQLPrimaryKeys Arguments
Parameter
Type
Default Value
Description
pkey_table_qualifier
VARCHAR
Current database
Name of the database from which the details are to be obtained.
table_name
VARCHAR
(no default value)
Name of the table whose primary key information is requested. Pattern matching is supported (see
String Search Patterns
).
Returned Result Set
Table 115
dbo.fSQLPrimaryKeys Returned Result Set
Column Name
Data Type
Description
TABLE_QUALIFIER
VARCHAR
Name of the database; NULL if not applicable to the database.
TABLE_OWNER
VARCHAR
Name of the owner of the primary key table; NULL if not applicable to the database.
TABLE_NAME
VARCHAR not NULL
Name of the primary key table
COLUMN_NAME
VARCHAR not NULL
Name of the primary key column. An empty string is returned for a column that does not have a name.
COLUMN_SEQ
SMALLINT not NULL
Column sequence number in key (starting with 1).
PK_NAME
VARCHAR
Name of the primary key; NULL if not applicable to the database.
Example
This example creates two tables in the DEMODATA sample database. Primary keys and foreign keys are assigned to the tables. The dbo.fSQLPrimaryKeys function references the two tables using a string search pattern. See also
String Search Patterns
.
CREATE TABLE tblprimarykey3 ( tblpk3col1 INT, tblpk3col2 INT, tblpk3col3 INT, tblpk3col4 INT, PRIMARY KEY (tblpk3col1, tblpk3col2) )
ALTER TABLE tblprimarykey3 ADD FOREIGN KEY (tblpk3col3, tblpk3col4) REFERENCES tblprimarykey3 ON DELETE CASCADE
CREATE TABLE tblprimarykey4 ( tblpk4col1 INT, tblpk4col2 INT, tblpk4col3 INT, tblpk4col4 INT, PRIMARY KEY (tblpk4col1, tblpk4col2) )
ALTER TABLE tblprimarykey4 ADD FOREIGN KEY (tblpk4col3, tblpk4col4) REFERENCES tblprimarykey4 ON DELETE CASCADE
SELECT * FROM dbo.fsqlprimarykeys('Demodata', 'tbl%')
Result Set
(abbreviated for space considerations):
TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME
============== =========== ======= =============
tblprimarykey3 tblpk3col1 1 PK_tblpk3col1
tblprimarykey3 tblpk3col2 2 PK_tblpk3col1
tblprimarykey4 tblpk4col1 1 PK_tblpk4col1
tblprimarykey4 tblpk4col2 2 PK_tblpk4col1
4 rows were affected.