How to retrieve all indexes and their column details from SQL Server database

Data and Databases
1

I need help getting information about indexes in my SQL Server 2008+ database. I want to extract all custom indexes along with their column information, but I’m having trouble with my current approach.

Here’s what I’ve tried so far:

select sch.name, tbl.name, idx.name, col.name 
from sys.tables tbl
join sys.schemas sch on tbl.schema_id = sch.schema_id
join sys.indexes idx on idx.object_id = tbl.object_id
join sys.index_columns idx_col on idx_col.object_id = tbl.object_id
join sys.columns col on col.object_id = tbl.object_id and
        idx_col.column_id = col.column_id

where idx.index_id > 0    
 and idx.type in (1, 2) -- only clustered and nonclustered
 and idx.is_primary_key = 0 -- exclude primary key indexes
 and idx.is_unique_constraint = 0 -- exclude unique constraints
 and idx.is_disabled = 0
 and idx.is_hypothetical = 0
 and idx_col.key_ordinal > 0

order by idx_col.key_ordinal

This query doesn’t give me exactly what I need. My goal is to get all user-created indexes (excluding those that support primary keys and unique constraints) along with their columns in the correct order they appear in the index definition. I also want to include as much additional metadata as possible about these indexes.

2

hey, try adding idx_col.index_id = idx.index_id in your query - that’ll do the trick. if ya need fragmentation info, check out sys.dm_db_index_physical_stats, super useful for that extra stuff.

3

You’re missing the join condition between sys.indexes and sys.index_columns. Add AND idx_col.index_id = idx.index_id to link the index columns with their indexes. Without it, you’ll get a cartesian product and wrong results. Want more complete index info? Join with sys.dm_db_index_usage_stats for usage stats, and grab idx.fill_factor, idx.has_filter, and idx.filter_definition. sys.partitions gives you row counts per index. Also throw in is_descending_key from sys.index_columns - it shows sort order for each column, which is super helpful when you’re looking at execution plans.

4

Wait, are you filtering out system tables too? You might be missing custom indexes on system objects. What additional metadata did you have in mind? Index size, usage stats, or structural info?