Obtaining directly created Indexes
Posted by: Uriel Nudelman
Date: February 15, 2018 03:37PM

Obtaining directly created Indexes
I’m trying to obtain all the INDEXES from a schema that where created directly.
Selecting the INFORMATION_SCHEMAS.STATISTICS and filtering the records from INFORMATION_SCHEMAS.TABLE_CONSTRAINTS, I obtain the indexes with the following query:
SELECT *
FROM INFORMATION_SCHEMA.STATISTICS S
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
ON T.TABLE_SCHEMA = S.TABLE_SCHEMA
AND T.TABLE_NAME = S.TABLE_NAME
AND T.CONSTRAINT_NAME = S.INDEX_NAME
WHERE S.TABLE_SCHEMA = DATABASE() AND T.CONSTRAINT_NAME IS NULL;
The problem here is that I got indexes that are related with foreign keys. I haven’t found any way to filter them out.
I tried using the INDEX_NAME initially but the found that if the foreign key is renamed the index name remains with the original name.
Any ideas how to find which indexes where directly created and with ones depends on other objects?
Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Obtaining directly created Indexes
360
February 15, 2018 03:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.