List of tables without indexes in sql 2008

Question

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

Edit
I want a schema name and a table name.

Answer 1

This should cover what you are looking for. that is, tables that are heaps (without a clustered index) and have no nonclustered indexes. He is using a new system. which were used in 2005/2008.

In addition, you probably want to find tables that have a clustered index but do not have nonclustered indexes (this is the second part of the instruction, which I left without comment.

SELECT schemaname = OBJECT_SCHEMA_NAME(o.object_id) ,tablename = o.NAME FROM sys.objects o 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)) 

Answer 2

Here is an example:

 select SCHEMA_NAME(schema_id), name from sys.tables where OBJECTPROPERTY(object_id, 'IsIndexed')= 0 

Answer 3

 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 updated SQL to include the schema name as requested. (Note that I had to use sys.objects instead of sysobjects to service the schemas that were introduced in SQL 2005)

Catalog tables are documented in the SQL Server documentation, see this link.
This FAQ contains more samples and may also be helpful.

Please note that these are system tables and can change between versions of the SQL server, where possible, instead of using system table independent expressions called Types of information schemas .

Answer 4

In addition to the @Philip Fourie suggestion, you can think about which indexes to create.

As soon as you access your data, SQL Server 2008 keeps track of the places where it considers the indexes to be useful (this refers to them as “missing indexes.” There is a hand full of new dynamically managed views that these missing indexes can show and some information about them.

From MSSQlTips :

• sys.dm_db_missing_index_details - returns detailed information about the 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 columns of the database table that are missing for the index. This is a function and requires passing an index.handle pointer.