Does anyone know where on the syste

[mattyp75]

Does anyone know where on the system tables i can find information as to whether a table has a primary key or not? I've tried using sysobects and sysindexes (keycnt), but this doesn't appear to be correct. I'm attempting to add primary keys to existing tables and want to check whether they exist before adding them to the neccessary tables.

The code i have is:
IF NOT EXISTS(SELECT * FROM dbo.sysobjects a JOIN dbo.sysindexes b ON a.id=b.id WHERE a.id = object_id(@DataName) AND a.name = @DataName AND b.keycnt > 0)
BEGIN
Do what I have to do
END

The variable @DataName is passed to the code by a cursor, but I have tables with no primary keys that don't get them added. Any help would be much appreciated,
Matt

 

[nigelrivett]

This wil give all fields that are part of the PK


declare @TableName varchar(128)
select @TableName = 'mytbl'

select c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

to get all tables without a primary key
select t.TABLE_NAME
from INFORMATION_SCHEMA.TABLES t
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
on c.TABLE_NAME = t.TABLE_NAME
where c.TABLE_NAME is null
and t.TABLE_TYPE = 'BASE_TABLE'

(no need to use a cursor (ever) for any of this)

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.