FIX: INFORMATION_SCHEMA.KEY_COLUMN_USAGE.ORDINAL_POSITION Column Reflects Incorrect Data

Article translations Article translations
Article ID: 278387 - View products that this article applies to.
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)
Expand all | Collapse all

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))
go
create 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))
go
select 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		     4
pk_t1		    t1		   c5		     5
fk_t2		    t2		   c2		     3
fk_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		      2
pk_t1		     t1		    c5		      1
fk_t2		     t2		    c2		      2
fk_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: October 21, 2013 - Revision: 2.1
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB278387

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com