Tuesday, March 07, 2006

Tables no primary key

Have you ever wondered how to find tables without primary key ? You can use the following statments to find one
use pubs
go
select o.Table_Name
FROM
(select name as Table_Name from sysobjects where xtype = 'U') o
LEFT OUTER JOIN
(
SELECT Table_Name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = 'PRIMARY KEY') pk
on pk.Table_Name = o.Table_Name
WHERE pk.Table_Name is null