SQL - Show indexes

SHOW INDEX是检索表格中确定的指数信息的基本指令。 然而,“SHOW INDEX”指挥系统只设在MySQL RDBMS,在QL服务器上不是有效的指挥。

在服务器中,系统存储程序“sp_helpindex”用于检索表格中确定的索引信息。 表格中载有每个指数的详细资料,包括姓名、类型和栏目。

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

以下是sp_helpindex。 储存在库克的系统

sp_helpindex [ @objname = ]  name 

这里,[@objname =] 姓名具体说明检索索引信息的表格名称。

在执行上述存储程序之后,它退回了包含以下信息的一套结果:

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 (pke 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,  Chaitap ,  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);

表格一经编制,让我们利用以下问询,在CUSTOMERS表中为“NAME”一栏编制索引。

SQL> CREATE INDEX INDEX_NAME on CUSTOMERS(NAME);

现在,让我们利用“sp_helpindex”所储存的系统,列出在消费物价指数表中建立的所有指数,如下所示:

SQL> EXEC sys.sp_helpindex @objname = N CUSTOMERS ;

Output

在执行上述询问时,产出如下:

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