Obtaining directly created Indexes
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
Subject
Written By
Posted
Obtaining directly created Indexes
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.