Category Archives: Tips/Tricks

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

How to get all the Tables with or without Primary Key Constraint in Sql Server?

We can write a query like below to get all the Tables with no Primary key constraint:

SELECT T.name 'Table without Primary Key'
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
      AND type = 'U'

We can write a query like below to get all the Tables with Primary key constraint:

SELECT T.name 'Table with Primary Key'
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 1
      AND type = 'U'

Let us understand this with example:

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
/*Let us create Customers table with Primary Key.*/
CREATE TABLE dbo.Customers (
	CustomerId int IDENTITY (1, 1) 
              PRIMARY KEY CLUSTERED NOT NULL ,
	FirstName Varchar(50),
	LastName Varchar(50))
GO

/*Let us create Orders Table without any primary key.*/
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 queries to get the list of all Tables with or without Primary Keys and verify the result:
List_All_Tables_With_And_Without_Primary_Keys

How to get all HEAP Tables or Tables without Clustered Index in Sql Server?

A Table that doesn’t have a Clustered Index is referred to as a HEAP Table. We can write a query like below to get all the HEAP Tables or tables that doesn’t have Clustered Index:

SELECT T.Name 'HEAP TABLE'
FROM sys.indexes I		
	INNER JOIN sys.tables T 
		ON I.object_id = T.object_id 
WHERE I.type = 0 AND T.type = 'U'

Let us understand this with an example:

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
/*Let us create a Non-HEAP Table Customers Table 
with clustered and Non-clustered index.*/
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 a HEAP Table 
(i.e. table without any Clustered 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 all the HEAP Tables or tables that doesn’t have Clustered Index and verify the result:

List_all_HEAP_Tables_or_Tables_without_Clustered_Index