Script to Generate All Indexes in a Database in SQL

1. Overview

Indexes play an important role in improving the performance of SQL queries. This allows faster data retrieval from tables. Indexes are especially important in databases with large volumes of data or frequent read operations. However, managing indexes across different tables and schemas can become challenging, especially in large systems.

To ease this process, it’s helpful to generate a list of all existing indexes in a database. This way, we can quickly audit index usage, identify duplicates, or ensure proper index coverage for query optimization.

In this tutorial, we’ll explore how to use an SQL script to retrieve all indexes in a database. We’ll explore implementation in PostgreSQL, MySQL, and SQL Server with the use of the Baeldung University database for practical illustration.

2. Implementation in PostgreSQL

PostgreSQL offers rich catalog views that expose metadata about tables, indexes, and other database objects. Specifically, the pg_indexes view provides a convenient way to fetch all defined indexes, including their schema, table, and definition. For example, let’s query all indexes created on the Baeldung University database using a straightforward SQL script:

SELECT 
    schemaname, 
    tablename, 
    indexname, 
    indexdef
FROM 
    pg_indexes
WHERE 
    schemaname = 'public'
ORDER BY 
    tablename, 
    indexname;
+------------+---------------+--------------------+---------------------------------------------------------------------------------+
| schemaname | tablename     | indexname          | indexdef                                                                        |
|------------+---------------+--------------------+---------------------------------------------------------------------------------|
| public     | course        | course_pkey        | CREATE UNIQUE INDEX course_pkey ON public.course USING btree (id)               |
| public     | department    | department_pkey    | CREATE UNIQUE INDEX department_pkey ON public.department USING btree (id)       |
| public     | exam          | exam_pkey          | CREATE UNIQUE INDEX exam_pkey ON public.exam USING btree (id)                   |
| public     | faculty       | faculty_pkey       | CREATE UNIQUE INDEX faculty_pkey ON public.faculty USING btree (id)             |
| public     | prerequisite  | prerequisite_pkey  | CREATE UNIQUE INDEX prerequisite_pkey ON public.prerequisite USING btree (id)   |
| public     | program       | program_pkey       | CREATE UNIQUE INDEX program_pkey ON public.program USING btree (id)             |
| public     | registration  | registration_pkey  | CREATE UNIQUE INDEX registration_pkey ON public.registration USING btree (id)   |
| public     | specification | specification_pkey | CREATE UNIQUE INDEX specification_pkey ON public.specification USING btree (id) |
| public     | student       | student_pkey       | CREATE UNIQUE INDEX student_pkey ON public.student USING btree (id)             |
| public     | teaching      | teaching_pkey      | CREATE UNIQUE INDEX teaching_pkey ON public.teaching USING btree (id)           |
+------------+---------------+--------------------+---------------------------------------------------------------------------------+
SELECT 10
Time: 0.012s

The query returns a list of all indexes in the public schema. Each row includes the schema name, table name, index name, and index definition. This output helps identify which indexes exist on tables. Although pg_indexes offers convenience, we can also retrieve index metadata directly from PostgreSQL’s system catalogs for more control:

SELECT 
    t.relname AS table_name,
    i.relname AS index_name,
    a.attname AS column_name
FROM 
    pg_class t
JOIN 
    pg_index ix ON t.oid = ix.indrelid
JOIN 
    pg_class i ON i.oid = ix.indexrelid
JOIN 
    pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
WHERE 
    t.relkind = 'r'
    AND t.relnamespace = 'public'::regnamespace
ORDER BY 
    t.relname, 
    i.relname;
+---------------+--------------------+-------------+
| table_name    | index_name         | column_name |
|---------------+--------------------+-------------|
| course        | course_pkey        | id          |
| department    | department_pkey    | id          |
| exam          | exam_pkey          | id          |
| faculty       | faculty_pkey       | id          |
| prerequisite  | prerequisite_pkey  | id          |
| program       | program_pkey       | id          |
| registration  | registration_pkey  | id          |
| specification | specification_pkey | id          |
| student       | student_pkey       | id          |
| teaching      | teaching_pkey      | id          |
+---------------+--------------------+-------------+
SELECT 10
Time: 0.015s

This version shows each index and the columns it covers, making it easier to analyze multi-column indexes or overlapping definitions.

3. Implementation in MySQL

MySQL also provides system views that expose metadata about indexes. The information_schema.STATISTICS table serves this purpose and contains one row per column in each index. Additionally, by querying the view, we can generate a complete list of indexes across the database. For example, let’s retrieve all indexes in the University database:

SELECT 
    TABLE_NAME, 
    INDEX_NAME, 
    COLUMN_NAME
FROM 
    information_schema.STATISTICS
WHERE 
    TABLE_SCHEMA = 'University'
ORDER BY 
    TABLE_NAME, 
    INDEX_NAME, 
    SEQ_IN_INDEX;
+---------------+-----------------------------------+-----------------+
| TABLE_NAME    | INDEX_NAME                        | COLUMN_NAME     |
+---------------+-----------------------------------+-----------------+
| Course        | course_department_id_fkey         | department_id   |
| Course        | id                                | id              |
| Course        | PRIMARY                           | id              |
| Department    | id                                | id              |
| Department    | PRIMARY                           | id              |
| Exam          | exam_course_id_fkey               | course_id       |
| Exam          | exam_student_id_fkey              | student_id      |
| Exam          | id                                | id              |
| Exam          | PRIMARY                           | id              |
| Faculty       | faculty_department_id_fkey        | department_id   |
| Faculty       | id                                | id              |
| Faculty       | PRIMARY                           | id              |
| Prerequisite  | id                                | id              |
...

This query filters the STATISTICS table to only include indexes from the University schema. It returns the table name, index name, and the column name involved in the index. The SEQ_IN_INDEX column ensures the correct order of columns in case of multi-column indexes. Furthermore, the output confirms that all tables have a primary key defined, which MySQL represents internally as a unique index named PRIMARY.

However, to gain more insight, such as determining whether an index is unique or identifying its type, we can extend the query:

SELECT 
    TABLE_NAME, 
    INDEX_NAME, 
    COLUMN_NAME,
    NON_UNIQUE,
    INDEX_TYPE
FROM 
    information_schema.STATISTICS
WHERE 
    TABLE_SCHEMA = 'University'
ORDER BY 
    TABLE_NAME, 
    INDEX_NAME, 
    SEQ_IN_INDEX;
+---------------+-----------------------------------+-----------------+------------+------------+
| TABLE_NAME    | INDEX_NAME                        | COLUMN_NAME     | NON_UNIQUE | INDEX_TYPE |
+---------------+-----------------------------------+-----------------+------------+------------+
| Course        | course_department_id_fkey         | department_id   |          1 | BTREE      |
| Course        | id                                | id              |          0 | BTREE      |
| Course        | PRIMARY                           | id              |          0 | BTREE      |
| Department    | id                                | id              |          0 | BTREE      |
| Department    | PRIMARY                           | id              |          0 | BTREE      |
| Exam          | exam_course_id_fkey               | course_id       |          1 | BTREE      |
| Exam          | exam_student_id_fkey              | student_id      |          1 | BTREE      |
| Exam          | id                                | id              |          0 | BTREE      |
| Exam          | PRIMARY                           | id              |          0 | BTREE      |
...

Here, the NON_UNIQUE column tells us whether the index enforces uniqueness (0 means unique), and INDEX_TYPE shows the indexing method used (usually BTREE for standard indexes). This extended output becomes especially useful when auditing for performance issues or checking whether unique constraints are enforced where expected.

4. Implementation in SQL Server

SQL Server provides several system views and catalog functions to retrieve index-related metadata. One of the most commonly used views is sys.indexes, which stores information about all indexes in the database. To get a comprehensive list of indexes across all tables, we can join this view with others like sys.tables, sys.columns, and sys.index_columns.

Let’s use an SQL Server script to retrieve all indexes in the University database:

SELECT 
    t.name AS table_name,
    ind.name AS index_name,
    col.name AS column_name,
    ind.is_unique,
    ind.type_desc AS index_type
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 
    t.is_ms_shipped = 0
ORDER BY 
    t.name, ind.name, ic.key_ordinal;
+---------------+--------------------------+-----------------+-----------+----------------------+
| table_name    | index_name               | column_name     | is_unique | index_type           |
+---------------+--------------------------+-----------------+-----------+----------------------+
| Course        | PK_Course                | id              | 1         | CLUSTERED            |
| Course        | IX_Course_DepartmentID   | department_id   | 0         | NONCLUSTERED         |
| Department    | PK_Department            | id              | 1         | CLUSTERED            |
| Exam          | PK_Exam                  | id              | 1         | CLUSTERED            |
| Exam          | IX_Exam_CourseID         | course_id       | 0         | NONCLUSTERED         |
| Exam          | IX_Exam_StudentID        | student_id      | 0         | NONCLUSTERED         |
| Faculty       | PK_Faculty               | id              | 1         | CLUSTERED            |
| Prerequisite  | PK_Prerequisite          | id              | 1         | CLUSTERED            |
| Prerequisite  | IX_Prereq_CourseID       | course_id       | 0         | NONCLUSTERED         |
| Prerequisite  | IX_Prereq_PrerequisiteID | prerequisite_id | 0         | NONCLUSTERED         |
...

The output shows a detailed list of all indexes in the University database, including their associated tables, columns, uniqueness, and index types.

5. Conclusion

In this article, we’ve explored how to retrieve index information in PostgreSQL, MySQL, and SQL Server using system catalog views and metadata tables. These queries provide insights into index definitions, uniqueness, and structure, which are essential for database optimization and maintenance.

Regularly auditing indexes can help identify redundant or missing indexes, ultimately improving query performance and system efficiency.