Menu
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.
Answers. In Management studio, 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.
ALL_IND_COLUMNS describes the columns of indexes on all tables accessible to the current user. Note: For join indexes, the TABLE_NAME and TABLE_OWNER columns in this view may not match the TABLE_NAME and TABLE_OWNER columns you find in the *_INDEXES (and other similar) data dictionary views.
Use SHOW INDEX like so:
SHOW INDEX FROM [tablename]
Docs: https://dev.mysql.com/doc/refman/5.0/en/show-index.html
Try:
SELECT * FROM information_schema.statistics
WHERE table_schema = [DATABASE NAME]
AND table_name = [TABLE NAME] AND column_name = [COLUMN NAME]
It will tell you if there is an index of any kind on a certain column without the need to know the name given to the index. It will also work in a stored procedure (as opposed to show index)
SHOW KEYS FROM tablename WHERE Key_name='unique key name'
you can find if there exists an unique key in the table
show index from table_name where Column_name='column_name';
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