FIX: INFORMATION_SCHEMA.KEY_COLUMN_USAGE.ORDINAL_POSITION Column Reflects Incorrect Data

This article was previously published under Q278387
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 58055 (SQLBUG_70)
SYMPTOMS
The ORDINAL_POSITION column in INFORMATION_SCHEMA.KEY_COLUMN_USAGE returns incorrect results. For example:
create table t1 (c1 int not null, c2 int not null, c3 char(5), c4 int, c5 int, constraint pk_t1 PRIMARY KEY (c5,c4))gocreate table t2 (tc1 int not null, c1 int not null, c2 int not null, c5 char(5), constraint fk_t2 FOREIGN KEY (c1,c2) references t1 (c5,c4))goselect CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION from information_schema.key_column_usage where TABLE_NAME in ('t1', 't2')				
The results are:
CONSTRAINT_NAME    TABLE_NAME     COLUMN_NAME       ORDINAL_POSITION  ---------------    ----------     -----------       ----------------pk_t1		    t1		   c4		     4pk_t1		    t1		   c5		     5fk_t2		    t2		   c2		     3fk_t2		    t2		   c1		     2				
Note that the values for the ORDINAL_POSITION column are incorrect. It should return the relative position of the column in the constraint definition.

The correct results should be:
CONSTRAINT_NAME    TABLE_NAME     COLUMN_NAME        ORDINAL_POSITION  ---------------    ----------     -----------        ----------------pk_t1		     t1		    c4		      2pk_t1		     t1		    c5		      1fk_t2		     t2		    c2		      2fk_t2		     t2		    c1		      1				
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
Properties

Article ID: 278387 - Last Review: 10/21/2013 03:15:24 - Revision: 2.1

  • Microsoft SQL Server 7.0 Standard Edition
  • kbnosurvey kbarchive kbbug kbfix KB278387
Feedback