Primary Keys

Primary Keys

Post by Andy Reill » Fri, 30 Mar 2001 19:58:05



I am trying to get information out of SQL 7 system tables into a user table.  The
info I'm looking for is the col. Numbers that a primary key includes I.e. if you
are looking at a table for customers the col no that related to the customerID.  
I have been able to get the name and ID number from sysobjects along with the ID
of the parent table. I hope this makes sense.

Andy

 
 
 

Primary Keys

Post by Allan » Fri, 30 Mar 2001 23:54:31


Andy

Rule 1:  Don't directly query the system tables if you can get away with it as
they are the property of MS and they can change so your routines will *out.

Try

select
        A.CONSTRAINT_NAME,
        A.TABLE_NAME,
        A.COLUMN_NAME,
        A.ORDINAL_POSITION
from
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS B
        ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE
        B.CONSTRAINT_TYPE = 'PRIMARY KEY'

Allan

Quote:-----Original Message-----

I am trying to get information out of SQL 7 system tables into a user table.  The
info I'm looking for is the col. Numbers that a primary key includes I.e. if you
are looking at a table for customers the col no that related to the customerID.  
I have been able to get the name and ID number from sysobjects along with the ID
of the parent table. I hope this makes sense.

Andy
.