How to query for table without primary key

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';

 

Technorati-Tags: SQL,MS SQL Server