list of tables without indexes in sql 2008

Question

How do I list tables without indexes in my SQL 2008 database?

Edit
I want the Schema name and the Table name.

Answer 1

select shema = s.name, table_name = o.name
from sys.objects o 
join sys.schemas s on o.schema_id = s.schema_id
where type = 'U'
and not exists (select i.index_id 
                from sys.indexes i 
                where i.type <> 0 --ignore default heap index row
                and o.object_id = i.object_id )

Edit:
I have updated the SQL to include the Schema name as requested. (Note I had to sys.objects instead of sysobjects to cater for schemas that were introduced in SQL 2005)

The catalog tables are documented in the SQL Server documentation, see this link.
This FAQ contains more samples and might also be useful.

Note that these are system tables and can change between SQL server versions, where possible rather use the system table-independent views called Information Schema Views.

Answer 2

In addition to @Philip Fourie's suggestion you might want to think about which indexes to create.

Once you have been accessing your data, SQL Server 2008 keeps track of places where it thinks indexes will be helpful (it refers to these as "missing indexes." There are a hand full of new Dynamic Managed Views which can show these missing indexes and some info about them.

From MSSQlTips:

• sys.dm_db_missing_index_details - Returns detailed information about a missing index
• sys.dm_db_missing_index_group_stats - Returns summary information about missing index groups
• sys.dm_db_missing_index_groups - Returns information about a specific group of missing indexes
• sys.dm_db_missing_index_columns(index_handle) - Returns information about the database table columns that are missing for an index. This is a function and requires the index_handle to be passed.

Answer 3

This should cover what your looking for. i.e. tables that are heaps (no clustered index) and do not have any non-clustered indexes. It uses the new sys. table objects used in 2005/2008.

in addition, you probably want to look for tables that do have a clustered index, but have no nonclustered indexes (this is the 2nd part of the statement which I've left commented out.

SELECT schemaname=s.name, tablename=o.name
    FROM sys.objects o INNER JOIN sys.schemas s ON s.schema_id=o.schema_id
            INNER JOIN sys.indexes i ON i.OBJECT_ID=o.OBJECT_ID
    -- tables that are heaps without any nonclustered indexes
    WHERE
    ( o.type='U'
            AND o.OBJECT_ID NOT IN (
        SELECT OBJECT_ID
            FROM sys.indexes
            WHERE index_id >0))
    --    OR
    -- table that have a clustered index without any nonclustered indexes
    --(o.type='U' 
    --        AND o.OBJECT_ID NOT IN (
    --    SELECT OBJECT_ID 
    --        FROM sys.indexes 
    --        WHERE index_id>1))