Today this question came up for a fair-sized database: Which tables don't have a primary key? Rather than click around forever in Enterprise Manager or Management Studio, I decided to write a query to quickly find all the offending tables:
-- Find all tables that don't have a Primary Key
select name FROM SysObjects WHERE xtype='U' AND id NOT IN
(SELECT parent_obj FROM SysObjects WHERE xtype='PK')
How does it work? SysObjects contains a row for every object in the database, including views, stored procedures, system tables, etc. The 'U' refers to user tables, and 'PK' refers to Primary Keys.
Here's a similar query that brings in the SysRelationships table to find all tables that don't participate in any relationship:
-- Find lone tables that don't participate in any relationships
SELECT name FROM SysObjects WHERE xtype='U' AND id NOT IN (SELECT fkeyID FROM SysForeignKeys UNION SELECT rkeyID FROM SysForeignKeys)
There's some good validation you can do very quickly with these kinds of queries.
If anyone has a request for a similar query then just add a comment, and I'll try to figure it out for ya.