How to Check What Indexes Exist on a SQL Server Table

The fastest way to see all indexes on a table

Open SQL Server Management Studio, connect to your database, and run this query in a new query window:

SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('YourTableName')

Replace 'YourTableName' with the actual name of your table. This query returns every index on that table, including the clustered index (the main sort order of the table) and any nonclustered indexes (extra lookup paths you created). You will see the index name, type, and whether it is unique or disabled.

If you want to see which columns each index uses, add this query instead:

SELECT i.name AS IndexName, c.name AS ColumnName FROM sys.indexes i JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE i.object_id = OBJECT_ID('YourTableName') ORDER BY i.name, ic.key_ordinal

This shows you the exact columns in each index and the order they appear in.

Key Takeaways

The sys.indexes system table holds information about every index on every table, and you query it by matching the table name to its object ID.
A clustered index defines the physical sort order of the table itself; a nonclustered index is an extra lookup path that points back to the main table.
The index_columns system table tells you which columns belong to each index and in what order, which matters because index performance depends on column sequence.
Management Studio also has a graphical way to view indexes under the table name in Object Explorer, useful if you prefer not to write queries.

Using the graphical method in Management Studio

If you prefer clicking instead of typing queries, expand your database in the Object Explorer panel on the left side of Management Studio. Find your table, expand it, and look for the Indexes folder. Click on it to see all indexes listed by name.

Right-click any index name and select Properties to see details: which columns it uses, whether it is unique, whether it allows nulls, and whether it is currently disabled. This view is slower than a query for large numbers of indexes, but it is easier to read if you only need to check one or two tables.

What the index information actually tells you

When you run the query or open the Properties window, you will see several pieces of information. The index type shows whether it is clustered (1) or nonclustered (2). Every table has exactly one clustered index — usually on the primary key — which determines how the data is physically stored on disk. Nonclustered indexes are optional and exist only to speed up specific queries.

The is_unique field shows whether the index enforces uniqueness: a unique index means no two rows can have the same value in those columns. The is_disabled field tells you if the index exists but is turned off, which means SQL Server maintains it but does not use it for queries. A disabled index still takes up disk space and slows down inserts and updates, so it is usually dropped rather than left disabled.

The fill_factor shows how full each page of the index is allowed to get before a new page is created. A fill factor of 100 means pack the page completely; a fill factor of 80 means leave 20 percent empty space. Lower fill factors leave room for future inserts without reorganizing the index, which can improve performance on tables that grow frequently.

Finding indexes through the Properties window

Right-click the table name in Object Explorer and select Properties. Go to the Storage tab, then click Indexes. This opens a list of all indexes on that table with basic information: name, type, and whether it is unique or clustered.

This method is slower than a query but requires no SQL knowledge. It is useful if you are new to SQL Server or if you need to show someone else what indexes exist without opening a query window.

Checking index fragmentation and size

Knowing an index exists is one thing; knowing whether it is actually helping is another. Run this query to see how fragmented each index is and how much space it uses:

SELECT i.name AS IndexName, ips.avg_fragmentation_in_percent, ips.page_count FROM sys.indexes i JOIN sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('YourTableName'), NULL, NULL, 'LIMITED') ips ON i.object_id = ips.object_id AND i.index_id = ips.index_id ORDER BY ips.avg_fragmentation_in_percent DESC

Fragmentation above 10 percent usually means the index needs rebuilding; fragmentation between 5 and 10 percent can be reorganized. The page_count column shows how many 8-kilobyte pages the index uses on disk. A large index that is heavily fragmented is a candidate for rebuilding.

Viewing indexes for all tables at once

If you need to see indexes across your entire database, remove the WHERE clause from the first query:

SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName, i.type_desc FROM sys.indexes i WHERE database_id = DB_ID() ORDER BY OBJECT_NAME(i.object_id), i.name

This returns every index on every table in the current database, grouped by table name. It is useful for auditing, finding duplicate indexes, or understanding the overall index strategy on a database you just inherited.

Frequently Asked Questions

What is the difference between a clustered and nonclustered index?

A clustered index defines the physical order of the table on disk — the table is sorted by that index. Every table has exactly one. A nonclustered index is an extra lookup path that points to rows in the table; you can have many of them. Nonclustered indexes speed up specific queries without changing how the table is stored.

Can I delete an index I do not recognize?

Not safely without testing. First, check whether any process queries depend on it by running a trace or checking the process code. Deleting a clustered index is dangerous and rebuilds the entire table. Deleting a nonclustered index is usually safe but may slow down specific queries. Always test on a copy of the database first.

Why would an index be disabled?

An index is usually disabled when someone suspects it is not being used or is slowing down inserts and updates more than it helps queries. A disabled index still takes up space and slows maintenance, so it should be dropped rather than left disabled. You can check if an index is being used by querying sys.dm_db_index_usage_stats.

How do I know if an index is actually being used?

Run this query to see which indexes have been read or written since the server last restarted: SELECT i.name, ius.user_seeks, ius.user_scans, ius.user_lookups FROM sys.indexes i LEFT JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id WHERE i.object_id = OBJECT_ID('YourTableName'). An index with zero seeks, scans, and lookups is not being used and is a candidate for deletion.

What does fill factor mean and should I change it?

Fill factor controls how full each page of the index gets before splitting. A fill factor of 100 packs pages completely; 80 leaves 20 percent empty. Lower fill factors help if you insert many rows into the middle of the index, because the empty space prevents constant page splits. The default of 90 works for most tables; change it only if you see high fragmentation on a table that grows frequently.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.