Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

List All Tables Without An Index In Your SQL Server Database

From Wiki

Jump to: navigation, search

It is pretty easy to list all tables without any type of index in your database, we can use the objectpropertyex function for this. You would use the function as follows

objectpropertyex(object_id,'TableHasIndex')

Let's look at some code. Run the code below which will create a database and four tables, three tables will have an index and one table will not

  1. CREATE DATABASE test5
  2. GO
  3.  
  4. USE test5
  5. GO
  6.  
  7. --PK index
  8. CREATE TABLE Test1 (id INT NOT NULL  PRIMARY KEY)
  9. GO
  10.  
  11.  
  12. -- No index
  13. CREATE TABLE Test2 (id INT)
  14. GO
  15.  
  16. --Nonclustered index
  17. CREATE TABLE Test3 (id INT)
  18. GO
  19.  
  20. CREATE NONCLUSTERED INDEX ix_test3 ON Test3(id)
  21.  
  22.  
  23. --Clustered index
  24. CREATE TABLE Test4 (id INT)
  25. GO
  26.  
  27. CREATE CLUSTERED INDEX ix_test4 ON Test4(id)
  28. GO

Below is the query that will return all the tables with their creation and modification dates, that don't have an index

  1. select name As TableName,create_date as DateCreated, modify_date as DateModified
  2. from sys.tables
  3. where objectpropertyex(object_id,'TableHasIndex') = 1


Output:

Test2	2010-04-15 14:05:17.033	2010-04-15 14:05:17.033

In this case the query returns Test2, this is the table that doesn't have an index


Contributed by: --SQLDenis 18:15, 15 April 2010 (GMT)

Part of SQL Server Admin Hacks

713 Rating: 2.8/5 (29 votes cast)