Query 1 – Tables with primary keys

 
 

SQL 2005

SELECT c.name, b.name, a.name

FROM sys.key_constraints a

INNER JOIN sys.tables b ON a.parent_object_id = b.OBJECT_ID

INNER JOIN sys.schemas c ON a.schema_id = c.schema_id

WHERE a.type = ‘PK’

 
 

SQL 2000

SELECT c.name, a.name, b.name

FROM sysobjects a

INNER JOIN sysindexes b ON a.id = b.id

INNER JOIN sysusers c ON a.uid = c.uid

WHERE (b.status & 2048)<>0

 
 

Query 2 – Tables without primary keys

 
 

SQL 2005

SELECT c.name, b.name

FROM sys.tables b

INNER JOIN sys.schemas c ON b.schema_id = c.schema_id

WHERE b.type = ‘U’

AND NOT EXISTS

(SELECT a.name

FROM sys.key_constraints a

WHERE a.parent_object_id = b.OBJECT_ID

AND a.schema_id = c.schema_id

AND a.type = ‘PK’ )

 
 

SQL 2000

SELECT c.name, a.name

FROM sysobjects a

INNER JOIN sysusers c ON a.uid = c.uid

WHERE xtype = ‘U’

AND NOT EXISTS

(SELECT b.name

FROM sysindexes b

WHERE a.id = b.id

AND (b.status & 2048)<>0)

 
 

Query 3 – Tables with primary keys non clustered

 
 

SQL 2005

SELECT c.name, b.name, a.name

FROM sys.key_constraints a

INNER JOIN sys.tables b ON a.parent_object_id = b.OBJECT_ID

INNER JOIN sys.schemas c ON a.schema_id = c.schema_id

WHERE a.type = ‘PK’

AND a.unique_index_id <> 1

 
 

SQL 2000

SELECT c.name, a.name

FROM sysobjects a

INNER JOIN sysusers c ON a.uid = c.uid

WHERE xtype = ‘U’

AND EXISTS

(SELECT b.name

FROM sysindexes b

WHERE a.id = b.id

AND b.indid <> 1

AND (b.status & 2048)<>0)