What are all the tables without an index?

This is specific to Microsoft SQL Server 2005

  • All tables SHOULD have at least one index.

Summary

How to get the list of all tables without an index with MS SQL server 2005?

  • You cannot view all the tables when using Visual Studio, you must do it one table at a time.

Missing indexes

use sql911;
go

SELECT SCHEMA_NAME(schema_id) AS 'Schema Name', name AS 'Table Name'
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
ORDER BY 1,2;

Schema Name  Table Name
------------ ------------
dbo          DEPARTMENT
dbo          EMP_WORK
dbo          Emps
dbo          sales
dbo          sales02

(5 row(s) affected)
  • Do not forget to first select the database that you want to use.
  • This will give you all the tables without an index.
  • This includes all the schemas.
  • After this you should look at your design and see if it can improved.