How to Check Indexes on a Table

How to Check Indexes on a Table

Lets create a table named Dummy and insert few rows in it.

CREATE TABLE Dummy (
    ID int,
    LastName varchar(255),
    FirstName varchar(255),
    City varchar(255)
);

insert into Dummy values (1, 'l_1', 'f_1','c_1');
insert into Dummy values (2, 'l_2', 'f_2','c_2');
insert into Dummy values (3, 'l_3', 'f_3','c_3');
insert into Dummy values (4, 'l_4', 'f_4','c_4');
insert into Dummy values (5, 'l_5', 'f_5','c_5');
insert into Dummy values (6, 'l_6', 'f_6','c_6');

Select * from Dummy

ID	LastName	FirstName	City
1	l_1	f_1	c_1
2	l_2	f_2	c_2
3	l_3	f_3	c_3
4	l_4	f_4	c_4
5	l_5	f_5	c_5
6	l_6	f_6	c_6

We can see below 4 different queries to know about indexes on a table. Lets run all 4 below queries before creating an index on our table 'Dummy'.

1)
First query to know the indexes on a particular table

select * from sys.indexes
where object_id in (select object_id 
                    from sys.objects 
                    where name = 'Dummy')

Result:


2)
Second query to know indexes on a table

EXEC sys.sp_helpindex @objname = N'Dummy'

Result:
The object 'Dummy' does not have any indexes, or you do not have permissions.

3)
Third query to know all the tables without an index.

SELECT name
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0

Result:

Name
Dummy

4)
Fourth query to get indexes for User Created Tables.

SELECT
            so.name AS TableName
            , si.name AS IndexName
            , si.type_desc AS IndexType
FROM
            sys.indexes si
            JOIN sys.objects so ON si.[object_id] = so.[object_id]
WHERE
            so.type = 'U'    --Only get indexes for User Created Tables
            AND si.name IS NOT NULL
            AND so.name = 'Dummy'
ORDER BY
            so.name, si.type

Result:

TableName	IndexName	IndexType
-	-	-

Now lets create an Index on 'LastName' column of table 'Dummy':

CREATE INDEX idx_lastname
ON Dummy (LastName);

Lets run all the 4 queries again after creating an index on a table.

1)
First query to know the indexes on a particular table

select * from sys.indexes
where object_id in (select object_id 
                    from sys.objects 
                    where name = 'Dummy')

Result:


2)
Second query to know indexes on a table

EXEC sys.sp_helpindex @objname = N'Dummy'

Result:

index_name	index_description	index_keys
idx_lastname	nonclustered located on PRIMARY	LastName

3)
Third query to know all the tables without an index.

SELECT Name
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0

Result:

Name
-

4)
Fourth query to get indexes for User Created Tables.

SELECT
            so.name AS TableName
            , si.name AS IndexName
            , si.type_desc AS IndexType
FROM
            sys.indexes si
            JOIN sys.objects so ON si.[object_id] = so.[object_id]
WHERE
            so.type = 'U'    --Only get indexes for User Created Tables
            AND si.name IS NOT NULL
            AND so.name = 'Dummy'
ORDER BY
            so.name, si.type

Result:

TableName	IndexName	IndexType
Dummy	idx_lastname	NONCLUSTERED

Also See:
Return indexes for a particular table
Query to check index on a table