![SQL Logo](https://www.tutorialspoint.com/sql/images/sql-mini-logo.jpg)
- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Database
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 | | +----------------------------------+---------------------+------------------+