Query to Check Index on a Table

How to see indexes for a database or table in MySQL?

To see the index for a specific table use SHOW INDEX:

SHOW INDEX FROM yourtable;

To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA:

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';

Removing the where clause will show you all indexes in all schemas.

How to determine if an Index is required or necessary

I use Jason Strate's index analysis scripts. They tell you how much your existing indexes are used as well as how much missing indexes would have been used. I typically don't add indexes unless they make up more than 5 or 10% of the queries on a table.

Most importantly, though, it's about making sure the application responds fast enough for the users.

Jason Strate's index analysis blog articles)

These days, I use sp_BlitzIndex® when performing index analysis.

List of all index & index columns in SQL Server DB

There are two "sys" catalog views you can consult: sys.indexes and sys.index_columns.

Those will give you just about any info you could possibly want about indices and their columns.

EDIT: This query's getting pretty close to what you're looking for:

SELECT 
     TableName = t.name,
     IndexName = ind.name,
     IndexId = ind.index_id,
     ColumnId = ic.index_column_id,
     ColumnName = col.name,
     ind.*,
     ic.*,
     col.* 
FROM 
     sys.indexes ind 
INNER JOIN 
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN 
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
     sys.tables t ON ind.object_id = t.object_id 
WHERE 
     ind.is_primary_key = 0 
     AND ind.is_unique = 0 
     AND ind.is_unique_constraint = 0 
     AND t.is_ms_shipped = 0 
ORDER BY 
     t.name, ind.name, ind.index_id, ic.is_included_column, ic.key_ordinal;

How to display SQL Server table indexes?

How about this:

SELECT 
    TableName = t.Name,
    i.*
FROM 
    sys.indexes i
INNER JOIN 
    sys.tables t ON t.object_id = i.object_id
WHERE
    T.Name = 'YourTableName'

If you need more information (like columns contained in the index, their datatype etc.) - you can expand your query to something like this:

SELECT 
    TableName = t.Name,
    IndexName = i.Name, 
    IndexType = i.type_desc,
    ColumnOrdinal = Ic.key_ordinal,
    ColumnName = c.name,
    ColumnType = ty.name
FROM 
    sys.indexes i
INNER JOIN 
    sys.tables t ON t.object_id = i.object_id
INNER JOIN 
    sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN 
    sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
INNER JOIN 
    sys.types ty ON c.system_type_id = ty.system_type_id
WHERE 
    t.name = 'YourTableName' 
ORDER BY
    t.Name, i.name, ic.key_ordinal

These system catalog views contain a wealth of information about your system....

How do I get information about an index and table owner in Oracle?

According to the docs, you can just do:

select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from USER_INDEXES

or

select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from ALL_INDEXES

if you want all indexes...

How to list indexes created for table in postgres

The view pg_indexes provides access to useful information about each index in the database, eg.

select *
from pg_indexes
where tablename not like 'pg%';

query to find primary index for a progress table (openegde V11.6)

This is working in 11.6 as well. See the following query:

select "_index-name" from PUB."_index" idx, PUB."_file" fi where fi."_file-name"='Customer' and idx.rowid =(select"_file"."_prime-index" from PUB."_file" fs where fs."_file-name"='Customer'); 

_Index-Name
--------------------------------
CustNum 

How to show indexes in Oracle SQL

If you have the privileges, you can use the ALL_INDEXES or USER_INDEXES views. The query would be:

SELECT  *
FROM    all_indexes
WHERE   table_name = 'COUNTRY';

If you want some information on the columns included in the index, you can select those from ALL_IND_COLUMNS. Documentation regarding these views can be found here Static Data Dictionary Views: ALL_ALL_TABLES to ALL_MVIEWS

---

Related Topics