Category Archives: Scripts

How to get all the Tables with or without Non-Clustered Indexes in Sql Server?

Scripts, Sql Server, Tips/Tricks, , ,

We can write a query like below to get all the Tables without any Non-Clustered indexes:

--List all the Tables with NO Non-Clustered Indexes
SELECT Name 'Tables without any Non-Clustered Indexes'
FROM SYS.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasNonclustIndex') = 0
		AND Type = 'U'

We can write a query like below to get all the Tables with Non-Clustered indexes:

--List all the Tables that have Non-Clustered Indexes
SELECT Name 'Tables with Non-Clustered Indexes'
FROM SYS.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasNonclustIndex') = 1
		AND Type = 'U'

Let us understand this with 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 with Clustered indexe only.*/
CREATE TABLE dbo.Orders (
	OrderId int IDENTITY (1, 1)
	      PRIMARY KEY CLUSTERED NOT NULL,
	CustomerId int NOT NULL ,
	CreationDT DATETIME NOT NULL)
GO
/*Let us create OrderItems Table without any indexes.*/
CREATE TABLE dbo.OrderItems (
	OrderItemId int IDENTITY (1, 1),
	OrderId int NOT NULL,
	Qty int NOT NUll)
GO

Now let us run the queries to get the list of all Tables with or without Non-Clustered indexes and verify the result:
List_All_Tables_With_OR_Without_NonClustered_Indexes

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

Scripts, Sql Server, Tips/Tricks, , , ,

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?

Scripts, Sql Server, Tips/Tricks, , , , ,

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