Menu
How do I see if my database has any indexes on it?
How about for a specific table?
964
You can use the sp_helpindex to view all the indexes of one table. And for all the indexes, you can traverse sys. objects to get all the indexes for each table.
go to the table you can see + symbol for the table click on that you can see Columns,Keys,Constraints,Triggers,Indexes,Statistics. If you have Indexes for the table after you click + symbol against Indexes you get the Index name with the column for which you declared index.
Enter a name for the index and select the index type from the list. Select the column or columns that you wish to index by checking the column name in the Index Columns list. You can remove a column from the index by removing the check mark from the appropriate column.
sp_helpindex is a system stored procedure which lists the information of all the indexes on a table or view. This is the easiest method to find the indexes in a table. sp_helpindex returns the name of the index, description of the index and the name of the column on which the index was created.
To see the index for a specific table use SHOW INDEX:
SHOW INDEX FROM yourtable; To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA:
SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'your_schema'; Removing the where clause will show you all indexes in all schemas.
124
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
