A simple question – what SQL query can be used to determine the primary key of a given table? I can easily query SQL Server's system tables to determine fields' default values, data types, names etc etc but not the primary key.
Any ideas?
create
table foo1 (a int primary key, b int);Thanks
Mirek
Thanks but your SQL (when corrected) will give you the primary key constraint and will not tell you which field(s) make up the primary key. eg:
create table foo1 (a int primary key, b int)
select * from sysobjects
where parent_obj=object_id('foo1')
and xtype='PK'
What I'm asking is - how do you use SQL to determine the field(s) which make up the primary key on a given table?
I expected the syscolumns table to have included primary key but this is not the case.
Any ideas?
SELECT tblcons.TABLE_NAME, keycoluse.CONSTRAINT_NAME, keycoluse.COLUMN_NAME, keycoluse.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblcons
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE keycoluse ON (tblcons.CONSTRAINT_CATALOG = keycoluse.CONSTRAINT_CATALOG) AND
(tblcons.CONSTRAINT_SCHEMA = keycoluse.CONSTRAINT_SCHEMA) AND (tblcons.TABLE_CATALOG = keycoluse.TABLE_CATALOG) AND (tblcons.TABLE_SCHEMA = keycoluse.TABLE_SCHEMA) AND
(tblcons.TABLE_NAME = keycoluse.TABLE_NAME) AND (tblcons.CONSTRAINT_NAME = keycoluse.CONSTRAINT_NAME)
WHERE tblcons.CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY tblcons.TABLE_NAME, keycoluse.CONSTRAINT_NAME, keycoluse.ORDINAL_POSITION
Brilliant! Jeff, this works perfectly even though it’s way over my head J
Many thanks
