Wednesday, January 28, 2009

Tip #38 – Getting Index Information via SQL

I previously discussed the various system tables which provide information about dictionary objects. In this tip I will go into more detail about the system.indexes table and system.indexfiles system tables.

The system.indexes table contains information about all of the indexes defined in the data dictionary. Since the information is accessed via a system table you can use a WHERE clause to obtain the information you need. You can even use full text search to locate all indexes which contain a field name. The following example retrieves a list of all the indexes in the customer table along with the index expression.


The system.indexes table also includes information about full text search (content) indexes. These fields are prefixed by Index_FTS_ and include; Min_Length, Delimiters, Noise, Drop_Chars and Conditional_Chars.

FTS Index Info

The system.indexfiles table provides information about the index files associated with the data dictionary. Like system.indexes the Parent field contains the name of the table the index file is associated with.


No comments: