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

SHOW INDEX FROM 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 −

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 now insert some values into the above created table using the following query −

INSERT INTO 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 −

CREATE INDEX INDEX_NAME ON CUSTOMERS(NAME);

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

SHOW INDEX FROM CUSTOMERS;

Output

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

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

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 −

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

Output

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