Finding the primary key of a table through the System Tables 1

[webuser]

Is there a way to find the Primary Key of a table through the System Tables? I know about 'ObjectProperty' and the 'IsPrimaryKey' parameter, but I can't figure out how to use it for my problem. I need to find the primary key field for a given table. That function does not seem to help me.

Thanks..

 

[vishnuprasad]

What u need to do is to refer two tables:
syscolumns and sysindex,system tables.

Use the "isprimarykey" for the sysindex table.Join the two on the ids and provide the name of the table.

If u have probs i can send u the query.

 

[vishnuprasad]

I thought this might further help u..Its an easy query.

select object_name(i.id) as TableName ,i.Name as Primarykey from sysindexes i ,sysobjects s where object_name(s.id) = i.name and s.xtype ='PK'

Hope this is OK

 

[webuser]

This was great, but I don't think it resolves my problem completely. I want to be able to get a list of the Primary Key FIELD for each table...So the output of the query should be something like this:

TableName | PrimaryKeyField

Employees EmployeeID
Orders OrderID
... ...

Thanks again...

 

[webuser]

I came across the ColumnProperty Function. This seems to be the direction I am going in, only there is no parameter for 'PrimaryKey'...

Select COLUMNPROPERTY (Object_ID(MyTable), 'MyField', 'IsPrimaryKey')

 

[webuser]

Perhaps I did not state my problem correctly. I would like a way to figure out what field/s is the Primary Key for a given table.

For example, if I have a table 'Employees', I would like to know which field is the Primary Key for that table...

Please help...

 

[vishnuprasad]

select distinct object_name(i.id) as TableName ,c.name as Columnname,
i.Name as Primarykey from sysindexes i
,sysobjects s ,syscolumns c
where object_name(s.id) = i.name
and i.id = c.id and s.xtype = 'PK'

For specific table name give the name/id of the specific tablename in the where condition.
" and i.id = 'table_name'".

 

[webuser]

I thank you for your response. Unfortunately, unless I did something wrong (I simply pasted your code in), it did not work. I got one line for every column in every table, with the primary key on every line, but this does not identify to me which COLUMN is the primary key. I don't need the NAME of the primary key. I need the name of the column/field that contains the primary key.

I know there is a sysconstraints table, and this lists all the constraints for a table, and I'm assuming the Primary Key constraint too, but I have not been able to figure out how to extract the field name from that constraint. Perhaps I'm on the wrong track...

 

[ColinM]

select * from information_schema.key_column_usage
where table_name = 'Employees'

The information schemas are views which sheild some of the complexity of the system tables. Microsoft recommends you query these views instead of directly querying the system tables, as the system tables are subject to change. (however the views don't always contain everything you need!)

 

[RickCole]

Do you have any naming conventions for the names of your Primary keys??

All our primary key names include 'PK' in their name. i.e. CUSTOMER_PK_FIELDS. If you do then the following procedure you can edit to do the job for you. Or at least maybe get you going in the right direction.

Rick.


CREATE PROCEDURE upz_get_pk_columns

@tablename varchar(30)

AS

declare @i int
declare @thiskey varchar(30)
declare @lastindid int
declare @indid int
declare @tablesource int
declare @indidwork int
declare @keys varchar(200)

select @tablesource = id from sysobjects where name = @tablename
select @indidwork = indid from sysindexes where id = @tablesource and name like '%PK%'
select @i = 1


select @thiskey = index_col(@tablename, @indidwork, @i)
while @i <= 16

begin
select @thiskey = index_col(@tablename, @indidwork, @i)
if @thiskey is null
goto keysdone

if @i = 1
select @keys = index_col(@tablename, @indidwork, @i)
else
select @keys = @keys + ' ' + index_col(@tablename, @indidwork, @i)

select @i = @i + 1
end

keysdone:
select @keys = @keys + ', '
select @keys

 

[webuser]

Thanks everyone! While this works out pretty well (Rick's idea), and we do have a naming convention, the problem is, we are developing an app that might be used by other comanies on databases that are not within are control, in other words, maybe no naming convention. So unfortunately, I think I still need a way to do this w/o a naming convention...

As for Colin's idea:
(select * from information_schema.key_column_usage
where table_name = 'Employees')

This seems to 'almost' work. I think it is giving me a list of all the unique constraints on a a table (which includes the primary key). For example, my 'Employees' table has it's primary key on a column named 'ID' but it also has a unique constraint on the 'ss#' field and that ('ss#') is showing up as a separate record with this query. On tables that only have one unique constraint, this is perfect though.
But if there is a way to do this query w/ ONLY the primary key showing up in the recordset, then I'm set. Is there?

 

[webuser]

I figured it out! Thank you all!

Colin put me on the right track. Here is what I got:

select Distinct kcu.column_name from information_schema.table_constraints tc
join information_schema.key_column_usage kcu
On (tc.Constraint_Name = kcu.Constraint_Name)
where tc.Constraint_Type = 'Primary Key'
And tc.table_name = 'MyTable'

This gives me one record for each Primary Key column in a table!

The only caveat is that it seems to be pretty slow. Normally, this would not matter, but I am planning on using this in Triggers in many tables, which can lead to major slowdowns. I'm sure that if the view can get it, there is some way of getting it through the system tables that might be faster. If anyone has ideas, it would be great.

Again, thanks everyone. I am extremely appreciative for your help.

 

[ColinM]

You could adapt the key_column_usage view and incorporate your own join, but.....

SELECT db_name() AS CONSTRAINT_CATALOG, user_name(c_obj.uid) AS CONSTRAINT_SCHEMA, c_obj.name AS CONSTRAINT_NAME, db_name()
AS TABLE_CATALOG, user_name(t_obj.uid) AS TABLE_SCHEMA, t_obj.name AS TABLE_NAME, col.name AS COLUMN_NAME,
CASE col.colid WHEN ref.fkey1 THEN 1 WHEN ref.fkey2 THEN 2 WHEN ref.fkey3 THEN 3 WHEN ref.fkey4 THEN 4 WHEN ref.fkey5 THEN 5 WHEN ref.fkey6
THEN 6 WHEN ref.fkey7 THEN 7 WHEN ref.fkey8 THEN 8 WHEN ref.fkey9 THEN 9 WHEN ref.fkey10 THEN 10 WHEN ref.fkey11 THEN 11 WHEN ref.fkey12
THEN 12 WHEN ref.fkey13 THEN 13 WHEN ref.fkey14 THEN 14 WHEN ref.fkey15 THEN 15 WHEN ref.fkey16 THEN 16 END AS ORDINAL_POSITION
FROM sysobjects c_obj, sysobjects t_obj, syscolumns col, sysreferences ref
WHERE permissions(t_obj.id) != 0 AND c_obj.xtype IN ('F ') AND t_obj.id = c_obj.parent_obj AND t_obj.id = col.id AND col.colid IN (ref.fkey1, ref.fkey2, ref.fkey3,
ref.fkey4, ref.fkey5, ref.fkey6, ref.fkey7, ref.fkey8, ref.fkey9, ref.fkey10, ref.fkey11, ref.fkey12, ref.fkey13, ref.fkey14, ref.fkey15, ref.fkey16) AND
c_obj.id = ref.constid
UNION
SELECT db_name() AS CONSTRAINT_CATALOG, user_name(c_obj.uid) AS CONSTRAINT_SCHEMA, i.name AS CONSTRAINT_NAME, db_name()
AS TABLE_CATALOG, user_name(t_obj.uid) AS TABLE_SCHEMA, t_obj.name AS TABLE_NAME, col.name AS COLUMN_NAME,
v.number AS ORDINAL_POSITION
FROM sysobjects c_obj, sysobjects t_obj, syscolumns col, master.dbo.spt_values v, sysindexes i
WHERE permissions(t_obj.id) != 0 AND c_obj.xtype IN ('UQ', 'PK') AND t_obj.id = c_obj.parent_obj AND t_obj.xtype = 'U' AND t_obj.id = col.id AND
col.name = index_col(t_obj.name, i.indid, v.number) AND t_obj.id = i.id AND c_obj.name = i.name AND v.number > 0 AND v.number <= i.keycnt AND
v.type = 'P'

Thats the key_column_usage view definition!! |-0

What about flagging them in the triggers and running a scheduled task provided you don't need immediate updating