viewing and modifying indexes

8.3.1 View and modify the index with Enterprise Manager


Select the database to view in Enterprise Manager, and then select the Tables & Indexes page box in the taskpad, the index information for all user tables in the database is displayed in the taskpad, as shown in Figure 8-11.








to view and modify the details of the index, you need to right-click on the table you want to view, select the Manage Indexes option from the All Tasks submenu from the shortcut menu, and the Index Management dialog box appears as shown in Figure 8-9. Select the index you want to view or modify, click the Edit button, and the Modify Index dialog box appears as shown in Figure 8-12.





in the Modify Index dialog box shown in Figure 8-12, you can modify most of the index settings and directly modify its SQL script simply by pressing "Edit SQL ..." button to display the SQL Script edit box as shown in Figure 8-13. The SQL script in which the index can be edited, tested, and run.





However, the name of the index cannot be modified in the SQL Script Editing dialog box in Figure 8-13, and the procedure's index name modification requires the use of a system stored procedure sp_rename. To modify the name of an index in Enterprise Manager and to change other information such as the filegroup to which it belongs, you need to do so in the table's Properties dialog box, as shown in Figure 8-14. It should be noted that the properties dialog shown in Figure 8-14 is called from the Modify Table structure dialog shown in Figure 7-4, not directly from the shortcut menu's Properties menu item.








8.3.2 sp_helpindex View indexes with stored procedures


sp_helpindex Stored procedures can return information for all indexes of a table. The syntax is as follows:


sp_helpindex [@objname =] ' name '


the [@objname =] ' name ' clause specifies the name of the table in the current database.


Example 8-4: View the index of the table orders.


exec sp_helpindex orders

The results of
operation are as follows








8.3.3: Change the index name with the stored procedure sp_rename


example 8-5 changes the index in the Orders table Orders_quan name is called Orders_quantity.


exec sp_rename ' orders. [Orders_quan] ', ' orders_quantity ', ' Index '

The results of
operation are as follows:


----------------------------------------------------------------------------------------------------------- -------------


caution:changing Any part of the object name could break scripts and stored procedures.


the index is renamed to ' Orders_quantity '.