SQL - Show indexes

---

The SHOW INDEX is the basic command to retrieve the information about the indexes that have been defined on a table. However, the “SHOW INDEX” command only works on MySQL RDBMS and is not a valid command in the 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.

If you are trying to use the “SHOW INDEX” command in SQL database management systems such as Microsoft SQL Server Management Studio (MSSMS), you will get the result as an error message because the command is not recognized by the SQL Server engine.

Syntax

Following is the syntax of the sp_helpindex system stored procedure in SQL −

sp_helpindex [ @objname = ] 'name'

Here, [ @objname = ] 'name' specifies the name of the table for which the index information is being retrieved.

After executing the above stored procedure, it returns a result set that contains the following information −

• 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

Let us create a table with the name CUSTOMERS in the SQL database using the CREATE statement as shown in the query below −

SQL> CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (20, 2),       
   PRIMARY KEY (ID)
 );

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

SQL> INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (1, 'Ramesh', '32', 'Ahmedabad', 2000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (2, 'Khilan', '25', 'Delhi', 1500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (3, 'kaushik', '23', 'Kota', 2000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (4, 'Chaitali', '25', 'Mumbai', 6500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (5, 'Hardik','27', 'Bhopal', 8500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (6, 'Komal', '22', 'MP', 9000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (7, 'Muffy', '24', 'Indore', 5500);

Once the table is created, let us create an index for the column named “NAME” in the CUSTOMERS table using the following query −

SQL> CREATE INDEX 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 −

SQL> 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        | NAME             |
|                                  | located on PRIMARY  |                  |
| PK__CUSTOMER__ 3214EC27755869D9  | clustered, unique,  | ID               |
|                                  | primary key located |                  |
|                                  | on PRIMARY          |                  |
+----------------------------------+---------------------+------------------+