query to check index on a table

Question

i need a query to see if a table already has any indexes on it.

Answer 1

If you're using MySQL you can run SHOW KEYS FROM table or SHOW INDEXES FROM table

Answer 2

On Oracle:

• Determine all indexes on table:

  SELECT index_name FROM user_indexes WHERE table_name = :table

• Determine columns indexes and columns on index:

  SELECT index_name , column_position , column_name FROM user_ind_columns WHERE table_name = :table ORDER BY index_name, column_order

References:

• ALL_IND_COLUMNS
• ALL_INDEXES

Answer 3

Most modern RDBMSs support the INFORMATION_SCHEMA schema. If yours supports that, then you want either INFORMATION_SCHEMA.TABLE_CONSTRAINTS or INFORMATION_SCHEMA.KEY_COLUMN_USAGE, or maybe both.

To see if yours supports it is as simple as running

select count(*) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS

EDIT: SQL Server does have INFORMATION_SCHEMA, and it's easier to use the their vendor-specific tables, so just go with it.