IsPrimaryKey returns 0 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

IsPrimaryKey returns 0

Author

Topic

cas_o
Posting Yak Master

154 Posts

Posted - 2006-07-27 : 10:55:53

select OBJECTPROPERTY(OBJECT_ID('pubs..UPKCL_auidind'), 'ISPRIMARYKEY') IS_PK

The above TSQL returns 0 for the pubs database on my SQL Server 2000, I don't understand why ???
I need a way of identifying the Primary Key column(s) in a given table, I'm trying to use INFORMATION_SCHEMA but I just can't figure it out.

Any ideas guys?

;-]... Quack Waddle

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-27 : 11:03:42

see
INFORMATION_SCHEMA.TABLE_CONSTRAINTS

==========================================
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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-27 : 11:06:55

Look for INFORMATION_SCHEMA.TABLE_CONSTRAINTS view

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-27 : 11:11:14

Madhivanan

Failing to plan is Planning to fail

cas_o
Posting Yak Master

154 Posts

Posted - 2006-07-27 : 11:54:37

Got it thanx:


SELECT 
	c.COLUMN_NAME, c.DATA_TYPE, p.CONSTRAINT_TYPE  
FROM 
	INFORMATION_SCHEMA.COLUMNS c 
	LEFT JOIN 
		(SELECT u.TABLE_NAME, u.COLUMN_NAME, t.CONSTRAINT_TYPE 
		FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u 
		JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t 
		ON u.CONSTRAINT_NAME = t.CONSTRAINT_NAME
		WHERE t.CONSTRAINT_TYPE = 'PRIMARY KEY') p 
	ON c.TABLE_NAME = p.TABLE_NAME AND c.COLUMN_NAME = p.COLUMN_NAME
WHERE 
	c.TABLE_NAME = 'authors'

;-]... Quack Waddle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources