Database Tables Without a Primary Key or Cluster Key

Here is a simple script to report all tables in a database with indicators for those without PKs or CKs.

WITH DataCTE

AS
(
SELECT
        SCHEMA_NAME(schema_id)           AS SchemaName
       ,name                                    AS TableName
       ,'PK'                                    AS KeyType
       ,CASE
              WHEN OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0 THEN 'X'
              ELSE ''
       END AS MissingIndicator
FROM
       sys.tables
UNION ALL
SELECT
        SCHEMA_NAME(schema_id)           AS SchemaName
       ,name                                    AS TableName
       ,'CK'                                    AS KeyType
       ,CASE
              WHEN OBJECTPROPERTY(OBJECT_ID,'TableHasClustIndex') = 0 THEN 'X'
              ELSE ''
       END AS MissingIndicator
FROM
       sys.tables
)

SELECT

       SchemaName
       ,TableName
       ,ISNULL([CK],'') AS MissingCK
       ,ISNULL([PK],'') AS MissingPK
FROM
       DataCTE
PIVOT
       (
       MIN(MissingIndicator)
       FOR KeyType IN
              (
              [CK],[PK]
              )
       ) pvtData
ORDER BY
       SchemaName
       ,TableName


Here is a sample execution of it against the AdventureWorks 2014 database. The ProductProductPhoto table has no CK in the database as provided at codeplex. I removed the PK on ProductSubcategory (which also is the CK) to test that bit of the script.