How to find all the tables with no indexes at all in Sql Server?

We can write a query like below to get all the Tables in the Database that don’t have any indexes:

SELECT Name 'Tables without any Indexes'
FROM SYS.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIndex')=0

Let us understand this with an example:

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
/*Let us create Customers table with Clustered 
and Non-Clustered Indexes.*/
CREATE TABLE dbo.Customers (
	CustomerId int IDENTITY (1, 1) 
	         PRIMARY KEY CLUSTERED NOT NULL ,
	FirstName Varchar(50),
	LastName Varchar(50))
GO
CREATE NONCLUSTERED INDEX IX_Customers 
	ON dbo.Customers(FirstName,LastName)
GO

/*Let us create Orders Table without any indexes.*/
CREATE TABLE dbo.Orders (
	OrderId int IDENTITY (1, 1) NOT NULL ,
	CustomerId int NOT NULL ,
	CreationDT DATETIME NOT NULL)
GO

Now let us run the query to get the list of all the tables with no indexes at all and verify the result:
List_All_Tables_Without_Any_Indexes

One thought on “How to find all the tables with no indexes at all in Sql Server?

Leave a Reply

Your email address will not be published. Required fields are marked *