Finding Primary Key Column in Script

johnpirkey · June 9th, 2003, 03:43 PM

Well, I am surely missing something so small here, i'm gonna shoot myself when someone posts my mistake. I need to find the column(s) that make up the Primary Key for a given table, in t-sql script. I can join to the sysobjects table once with the table name, then join to a second reference where the parent_obj value is equal to the table's id value. i restrict this join to 'xType = PK', so that i only get the primary key row from sysobjects back. now, the real question is - How in the world do i determine which COLUMN(s) are in the PK?!?!!?

Here's my testing table:

Code:

ID                     int  IDENTITY, Primary Key
REVIEW_ID              int  Foriegn Key
RATER_USER_ID          int  Foriegn Key
APPRAISAL_READ_ONLY    bit
CREATION_DATE          datetime
CREATION_USER          varchar
REVISION_DATE          datetime
REVISION_USER          varchar
REVISION_NO            int

Here's what i have thus far:

Code:

SELECT 
    o.id, 
    c.colid, 
    o.Name 'table', 
    c.Name 'column', 
    pk.Name 'pk name', 
    pk.id 'pk id', 
    'PK' = CASE 
        WHEN pk.xType IS NOT NULL THEN 1 
        ELSE 0 
        END 
FROM 
    sysobjects o 
    JOIN syscolumns c ON o.id = c.id 
    LEFT JOIN sysobjects pk ON c.id = pk.parent_obj 
WHERE 
    o.Name = 'Appraisal' 
    AND (pk.xType = 'PK' OR pk.xType IS NULL) 
ORDER BY 
    c.colOrder

what happens, is that either NONE of returned columns are showing as PK's, or ALL of them are. I thought maybe the answer was in the sysConstraints table, but i only got the columns that had constraints (not null, etc). btw - the SQL Books Online mentions the Status field in the sysConstraints table, as

"Pseudo-bit-mask indicating the status. Possible values include:
1 = PRIMARY KEY constraint.
2 = UNIQUE KEY constraint.
3 = FOREIGN KEY constraint.
4 = CHECK constraint.
5 = DEFAULT constraint.
16 = Column-level constraint.
32 = Table-level constraint."

Can i just say HOW helpful that was (*note extreme sarcasm there)

so, at first, i thought - great - i'll AND the status with 1 and if i get 1 back, then it's a PK. however, all columns returned were masked with 1 for some reason. or i AND'ed it incorrectly (which is totally possible).

btw - i just did some more testing with that thing - and you might be interested to know that for my table i'm using to test (1 field PK, 2 FK's), i ONLY get the two FK columns back, both are marked as Primary Keys, Foreign Keys, Defaults, and Column Level Restraints

Code:

select 
    c.name, 
    con.status & 1 'and 1', 
    con.status & 2 'and 2', 
    con.status & 3 'and 3', 
    con.status & 4 'and 4', 
    con.status & 5 'and 5', 
    con.status & 16 'and 16', 
    con.status & 32 'and 32' 
from sysconstraints con 
    join syscolumns c on con.colid = c.colid and c.id = con.id 
where con.id = 242099903

which yields:

Code:

name                 and 1       and 2       and 3       and 4       and 5       and 16      and 32      
------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
RATER_USER_ID             1           2           3           0           1          16           0 
REVIEW_ID                  1           2           3           0           1          16           0

so, sysindexes was my next guess - and since the documentation is lacking for that table, I wasn't able to completely figure out all the columns. Then i stumbled across the second reference to sysObjects and came up with what i have above.

I'm dying here. Any help out there?

Thanks!

John

MCSD(VB6)
http://www.stlvbug.org

Jeff Mason · June 9th, 2003, 04:07 PM

You might try using the INFORMATION_SCHEMA system views.

The INFORMATION_SCHEMA.KEY_COLUMN_USAGE view lists columns which participate in a foreign key or primary key constraint for each table in the database. Primary key constraints begin with the letters 'PK_' in the CONSTRAINT_NAME column (I think unless you have deliberately renamed them otherwise...)

These schmea are preferable to the using the various sys tables, as they are marginally better documented, and won't change... See BOL.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

ram2098 · June 9th, 2003, 04:17 PM

I made a small change to your query..I think this still works...

SELECT distinct
   o.Name 'table',
   pk.Name 'pk name',
   c.Name 'column'
FROM
   sysobjects o
   JOIN syscolumns c ON o.id = c.id
   inner JOIN sysconstraints cs ON c.colid = cs.colid
   LEFT JOIN sysobjects pk ON c.id = pk.parent_obj

WHERE
   o.Name = 'Appraisal' and cs.colid = 1
   AND (pk.xType = 'PK' OR pk.xType IS NULL)

johnpirkey · July 15th, 2003, 11:58 AM

Hrmm, well it works for tables with a single primary key column as the FIRST column, but it doesn't work on multi-columned primary keys. It simply returns the first column in the table, unfortunately, because of the colid = 1 value. there's no guarantee that the first column will always be the PK, and like i said before, by restricting to that column, it doesn't return the full PK, if it's complex.

thanks for the thoughts, though.

john

Quote:

quote:Originally posted by ram2098
I made a small change to your query..I think this still works...

SELECT distinct
o.Name 'table',
pk.Name 'pk name',
c.Name 'column'
FROM
sysobjects o
JOIN syscolumns c ON o.id = c.id
inner JOIN sysconstraints cs ON c.colid = cs.colid
LEFT JOIN sysobjects pk ON c.id = pk.parent_obj

WHERE
o.Name = 'Appraisal' and cs.colid = 1
AND (pk.xType = 'PK' OR pk.xType IS NULL)

MCSD(VB6)
http://www.stlvbug.org

FredT · May 21st, 2004, 09:06 AM

Quote:

quote:Originally posted by johnpirkey
Well, I am surely missing something so small here, i'm gonna shoot myself when someone posts my mistake. I need to find the column(s) that make up the Primary Key for a given table, in t-sql script. I can join to the sysobjects table once with the table name, then join to a second reference where the parent_obj value is equal to the table's id value. i restrict this join to 'xType = PK', so that i only get the primary key row from sysobjects back. now, the real question is - How in the world do i determine which COLUMN(s) are in the PK?!?!!?

You may want to try the following:-

Code:

SELECT t.name as 'table',i.name as 'index',it.xtype,
(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=1 and k.id=t.id)as 'column1',
(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=2 and k.id=t.id)as 'column2',
(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=3 and k.id=t.id)as 'column3',
(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=4 and k.id=t.id)as 'column4',
(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=5 and k.id=t.id)as 'column5',
(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=6 and k.id=t.id)as 'column6',
(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=7 and k.id=t.id)as 'column7',
(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=8 and k.id=t.id)as 'column8',
(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=9 and k.id=t.id)as 'column9',
(select c.name from syscolumns c inner join sysindexkeys k on k.indid=i.indid and c.colid = k.colid and c.id=t.id and k.keyno=10 and k.id=t.id)as 'column10'
from sysobjects t
inner join sysindexes i on i.id=t.id 
inner join sysobjects it on it.parent_obj=t.id and it.name = i.name
order by t.name, i.name

By adding the appropriate WHERE clause you can easily restrict it to Primary Keys only

Code:

WHERE it.xtype='PK'

or to a specific table

Code:

WHERE t.name='MyTable'

and so on.

calzone2 · August 26th, 2004, 12:56 PM

Try something like this:

select distinct tbl.name TableName, constrId.name PrimaryKeyName, col.name ColumnName, sik.keyno,
case when ix.indid = 1 then 'Clustered' else 'NonClustered' end Clustering

from sysobjects tbl join sysconstraints constr
on tbl.id = constr.id

and

tbl.xtype = 'U'
and constr.status & 0x0001 = 0x0001
join sysobjects constrId on
constrId.parent_obj = tbl.id and constrId.xtype = 'PK'
join sysindexes ix
on constrId.name = ix.name
and
ix.id = tbl.id
join sysindexkeys sik
on
sik.id = tbl.id and sik.indid = ix.indid
join syscolumns col on
col.id = tbl.id and sik.colid = col.colid

order by
tbl.name, constrId.name, sik.keyno

Use the sysindexkeys table to figure out the columns of the index. The index name (from sysindexes) should match up with the name from the second sysobjects table copy.

Ben Zhang · September 15th, 2004, 07:39 PM

You can use the folowing, if primary key id (colid) != 1

select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where TABLE_NAME = 'Appraisal'
and CONSTRAINT_NAME =
( select op.name from sysobjects o
        inner join sysobjects op on op.parent_obj = o.id
        where o.name = 'Appraisal'
        and op.xtype = 'PK' )

or change above statement to inner join

Ben Zhang

pgtips · September 16th, 2004, 07:47 AM

Quote:

quote:...I need to find the column(s) that make up the Primary Key for a given table, in t-sql script...

Maybe this is a daft question, but why can't you just use the built-in proc sp_helpindex?

robopolomsky · March 12th, 2006, 11:35 AM

This select was very helpfull for me:

SELECT syscolumns.colname
FROM sysconstraints, sysindexes, systables, syscolumns
WHERE sysconstraints.constrtype = 'P'
AND sysconstraints.tabid = systables.tabid
AND sysindexes.tabid = systables.tabid
AND sysindexes.idxname = sysconstraints.idxname
AND syscolumns.tabid = systables.tabid
AND syscolumns.colno in (
sysindexes.part1, sysindexes.part2, sysindexes.part3, sysindexes.part4,
sysindexes.part5, sysindexes.part6, sysindexes.part7, sysindexes.part8,
sysindexes.part9, sysindexes.part10, sysindexes.part11, sysindexes.part12,
sysindexes.part13, sysindexes.part14, sysindexes.part15, sysindexes.part16)

happygv · April 26th, 2007, 10:06 AM

See if this helps.

Code:

select object_name(id) as TableName, index_col(object_name(id), 1 ,1) as PrimaryKeyColumn 
    from sysobjects where xtype = 'u' and status > 0
    Order by object_name(id)

Cheers

_________________________
- Vijay G
Strive for Perfection