RSS Feed

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

No comments:

Post a Comment