Find Tables Without Clustered Index but with Prima

问题:

Is there an script to find Tables with no Clustered Indexes, but has Primary Keys on it for Sql server 2008 r2? Please let me know.

回答1:

Something like this:

SELECT
            so.name AS TableName,
            si.name AS IndexName,
            si.type_desc AS IndexType,
            si.is_primary_key
FROM
            sys.indexes si
            JOIN sys.tables so ON si.[object_id] = so.[object_id]
WHERE
            si.type IN (0, 2)
            AND si.is_primary_key=1
ORDER BY
            so.name

回答2:

select O.name
from sys.objects as O
  inner join sys.indexes as I1
    on O.object_id = I1.object_id
  inner join sys.indexes as I2
    on O.object_id = I2.object_id
where O.type = 'U' and         -- U = Table (user-defined)
      I1.type = 0 and          -- 0 = Heap
      I2.is_primary_key = 1

sys.objects (Transact-SQL)
sys.indexes (Transact-SQL)