FerencMantfeld
2nd March 2010, 08:43 PM
One often wonders which columns are indexed and the column order in any particular index. This could be particularly useful information to have as there can be significant performance benefits to queries if the comparison column is the leading column of an index.
To query the indexed columns for a given table in Sql Server:
exec sp_helpindex «TABLE_NAME»
This will give you the index names for the given table, the index type and the sequence of the columns in the index definition.
To query the indexed columns for a given table in Oracle:
select IC.Index_name, I.Index_type, IC.Column_position as COLPOS, IC.Column_name from user_ind_columns IC join user_indexes I on IC.index_name = I.index_name where upper(IC.table_name) = upper(«TABLE_NAME») order by 1,3
In each of the above, replace the «TABLE_NAME» with the actual name, in quotes. Note that in Oracle, the table name is usually upper case and in single quotes.
To query the indexed columns for a given table in Sql Server:
exec sp_helpindex «TABLE_NAME»
This will give you the index names for the given table, the index type and the sequence of the columns in the index definition.
To query the indexed columns for a given table in Oracle:
select IC.Index_name, I.Index_type, IC.Column_position as COLPOS, IC.Column_name from user_ind_columns IC join user_indexes I on IC.index_name = I.index_name where upper(IC.table_name) = upper(«TABLE_NAME») order by 1,3
In each of the above, replace the «TABLE_NAME» with the actual name, in quotes. Note that in Oracle, the table name is usually upper case and in single quotes.