Retrieving Constraint Information

Hello

I wrote this query to retrieve all constraints (primary keys, foreign keys, unique keys, checks) on a table.


SELECT
c.name AS name,
CASE
WHEN c.xtype = 'PK' THEN 'primary'
WHEN c.xtype = 'F' THEN 'foreign'
WHEN c.xtype = 'UQ' THEN 'unique'
WHEN c.xtype = 'C' THEN 'check'
END AS type,
tkt.name AS contable,
tkc.name AS confield,
fkt.name AS reftable,
fkc.name AS reffield,
com.text AS expr
FROM
sysobjects c
LEFT JOIN sysconstraints con ON con.constid = c.id
LEFT JOIN sysforeignkeys fks ON fks.constid = con.constid
LEFT JOIN sysobjects tkt ON tkt.id = con.id
LEFT JOIN syscolumns tkc ON tkc.id = tkt.id AND tkc.colid = con.colid
LEFT JOIN sysobjects fkt ON fkt.id = fks.rkeyid
LEFT JOIN syscolumns fkc ON fkc.id = fkt.id AND fkc.colid = fks.rkey
LEFT JOIN syscomments com ON com.id = c.id
WHERE
c.xtype IN ('PK', 'F', 'UQ', 'C')
AND tkt.name = '$table'
AND c.name = '$constraint'



It returns a row for each constraint which can be easilly stored in an associative array.


Array (
[name], //name of the constraint
[type], //(primary|foreign|unique|check)
[contable], //table the constraint is on
[confield], //field the constraint is on
[reftable], //referenced table, null if type!=foreign
[reffield], //referenced field, null if type!=foreign
[expr], //check expression, null if type!=check
)


Everything works as expected except for one issue. For primary keys and unique keys, the sysconstraints.colid field is always '0'. The sysconstraints.id field properly indicates the id of the table that the primary/unique key is on, however I have no way of knowing which column(s) the primary/unique key is on. According to the Transact-SQL reference, the sysconstraints.colid field is the "ID of the column on which the constraint is defined, 0 if a table constraint.". Therefore, it looks like primary/unique constraints are stored as table constraints instead as a primary/unique constraint. However the sysconstraints.status field indicates the type of constraint to be a primary constraint or a unique constraint, not a table constraint.


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.


Is there something I am missing? Or maybe there is a better way to find the columns of a primary key or unique key that I can integrate into my above query?

Thanks

-except10n