Thursday, August 21, 2014

Query for user tables without Primary Keys



SELECT sch.name AS SchemaName, obj.name AS TableName
       FROM sys.objects obj
          INNER JOIN sys.schemas sch
                ON obj.schema_id = sch.schema_id
          LEFT JOIN sys.objects pk
                ON  pk.type = 'PK'
                AND pk.parent_object_id = obj.object_id
WHERE  obj.is_ms_shipped = 0
AND pk.object_id IS  NULL
AND obj.type = 'U'