December 1, 2004 at 2:47 pm
How to programmatically determine the primarykey (or lack of it) for a given table? Please share your ideas.
Thanks
December 1, 2004 at 5:05 pm
Try this in Northwind (try varying the table_name in the WHERE clause) and you should be on the right track:
select a.constraint_name, b.column_name from information_schema.table_constraints a
inner join information_schema.constraint_column_usage b
on a.constraint_name = b.constraint_name
where constraint_type = 'primary key' and a.table_name = 'employeeterritories'
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 2, 2004 at 1:33 am
One might also consider the use of sp_pkeys or OBJECTPROPERTY(...'TableHasPrimaryKey')
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 2, 2004 at 7:51 am
Thank you. They worked!
December 2, 2004 at 9:12 pm
You might be interested in attempting this bit of T-SQL
select name From sysobjects where id not in (select b.id from sysconstraints b, sysobjects c where c.type = 'K' and c.id = b.constid) and type = 'U' order by name
December 3, 2004 at 2:51 am
There are surely more than one way to skin that cat when you go down and touch system objects directly.
SELECT
USER_NAME(so.uid) Owner
, so.name [Table]
FROM
sysobjects so
LEFT JOIN
(
SELECT
id
FROM
sysconstraints
WHERE
status & 7 = 1
) sc
ON
so.id = sc.id
WHERE
so.type = 'U'
AND
sc.id IS NULL
AND
so.name <> 'dtproperties'
ORDER BY
Owner, [Table]
should also work.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply