At my daily work I need to audit my tables for missing primary keys. This query uses the standardized “information_schema” schema, so it should probably work with other databases than MS SQL Server 2008.
SELECT
t.table_catalog
, t.table_schema
, t.table_name
, t.TABLE_TYPE
FROM information_schema.tables t
LEFT JOIN
(
SELECT
table_catalog
, table_schema
, table_name
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY'
) SubQuery ON
t.TABLE_CATALOG = SubQuery.TABLE_CATALOG AND
t.TABLE_NAME = SubQuery.TABLE_NAME AND
t.TABLE_SCHEMA = SubQuery.TABLE_SCHEMA
WHERE SubQuery.TABLE_CATALOG IS NULL AND t.TABLE_TYPE = 'BASE TABLE';
