Show Indexes

The SQL Show Index Statement

The SHOW INDEX is the basic SQL statement to retrieve the information about the indexes that have been defined on a table. However, the SHOW INDEX statement only works on MySQL RDBMS and is not a valid statement in the SQL Server.

To list the indexes created on a table in SQL Server, a system stored procedure sp_helpindex is used.

The result-set obtained from querying the SHOW INDEX statement on a MySQL table contains the index information.

Syntax

Following is the syntax of the SHOW INDEX statement in MySQL −

SHOWINDEXFROM table_name;

Example

Following example demonstrates the working of SHOW INDEX statement in MySQL. First, create a table with the name CUSTOMERS in the MySQL database using the CREATE query below −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(20,2),PRIMARYKEY(ID));

Let us now insert some values into the above created table using the following query −

INSERTINTO CUSTOMERS VALUES(1,'Ramesh','32','Ahmedabad',2000),(2,'Khilan','25','Delhi',1500),(3,'Kaushik','23','Kota',2000),(4,'Chaitali','25','Mumbai',6500),(5,'Hardik','27','Bhopal',8500),(6,'Komal','22','Hyderabad',9000),(7,'Muffy','24','Indore',5500);

Once the data is inserted, create an index for the column NAME in the CUSTOMERS table using the following query −

CREATEINDEX INDEX_NAME ON CUSTOMERS(NAME);

Now, you can list all the indexes that are defined on the CUSTOMERS table using the following query −

SHOWINDEXFROM CUSTOMERS;

Output

On executing the above query, the output is displayed as follows −

Table	Non_unique	Key_name	Seq_in_index	Column_name
customers	0	PRIMARY	1	ID
customers	1	index_name	1	NAME

Showing Indexes in SQL Server

In SQL server, the system stored procedure sp_helpindex is used to retrieve the information about the indexes that have been defined on a table. It returns the result as a table that contains detailed information about each index, including the name, type, and columns.

Syntax

Following is the basic syntax to list indexes defined on a table in SQL Server −

sp_helpindex [@objname=]'name'

Here, [ @objname = ] ‘name’ specifies the name of the table for which the index information is being retrieved. The index information includes −

• index_name is the names of the columns that are included in index.
• index_description is the brief description of the index such as the type of index (like clustered or non-clustered).
• index_keys is the keys that are included in the index.

Example

CREATEINDEX INDEX_NAME on CUSTOMERS(NAME);

Now, let us list all the indexes that are created on the CUSTOMERS table using the system stored procedure sp_helpindex as shown below −

EXEC sys.sp_helpindex @objname= N'CUSTOMERS';

Output

On executing the above query, the output is displayed as follows −

index_name	index_description	index_keys
INDEX_NAME	nonclustered located on PRIMARY	NAME
PK__CUSTOMER__ 3214EC27755869D9	clustered, unique, primary key located on PRIMARY	ID