List all indexes for all tables in a SQL Server database

To list all indexes for all user tables in a database, you can use this code (in this example we use AdventureWorks sample database):

USE AdventureWorks
GO
SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, o.name AS Table_Name
, i.name AS Index_Name
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = 'U'
ORDER BY o.name, i.type

List all SQL Server database indexes

The results show the database name, schema name, table name, index name and index type (CLUSTERED, NONCLUSTERED, XML, SPATIAL...) for all user-defined tables (type = 'U' in sys.objects).

Read previous blog post about how to list all fragmented indexes in a database:
Getting Table and Index names for fragmented indexes in SQL Server

If the post helped you, please share it:
Pin It

10 comments to List all indexes for all tables in a SQL Server database

  • Waqar

    Well. It have done work for me....!!

  • Janakiraman.N

    Very useful query sir

    Thank u!!!

  • Howard Rothenburg

    WITH indexCTE AS
    (
    SELECT st.object_id AS objectID
    , st.name AS tableName
    , si.index_id AS indexID
    , si.name AS indexName
    , si.type_desc AS indexType
    , sc.column_id AS columnID
    , sc.name + CASE WHEN sic.is_descending_key = 1 THEN ' DESC' ELSE '' END AS columnName
    , sic.key_ordinal AS ordinalPosition
    , CASE WHEN sic.is_included_column = 0 AND key_ordinal > 0 THEN sc.name ELSE NULL END AS indexKeys
    , CASE WHEN sic.is_included_column = 1 THEN sc.name ELSE NULL END AS includedColumns
    , sic.partition_ordinal AS partitionOrdinal
    , CASE WHEN sic.partition_ordinal > 0 THEN sc.name ELSE NULL END AS partitionColumns
    , si.is_primary_key AS isPrimaryKey
    , si.is_unique AS isUnique
    , si.is_unique_constraint AS isUniqueConstraint
    , si.has_filter AS isFilteredIndex
    , COALESCE(si.filter_definition, '') AS filterDefinition
    FROM sys.tables AS st
    INNER JOIN sys.indexes AS si
    ON si.object_id = st.object_id
    INNER JOIN sys.index_columns AS sic
    ON sic.object_id=si.object_id
    AND sic.index_id=si.index_id
    INNER JOIN sys.columns AS sc
    ON sc.object_id = sic.object_id
    and sc.column_id = sic.column_id
    )

    SELECT DISTINCT
    @@SERVERNAME AS ServerName
    , DB_NAME() AS DatabaseName
    , tableName
    , indexName
    , indexType
    , STUFF((
    SELECT ', ' + indexKeys
    FROM indexCTE
    WHERE objectID = cte.objectID
    AND indexID = cte.indexID
    AND indexKeys IS NOT NULL
    ORDER BY ordinalPosition
    FOR XML PATH(''),
    TYPE).value('.','varchar(max)'),1,1,'') AS indexKeys
    , COALESCE(STUFF((
    SELECT ', ' + includedColumns
    FROM indexCTE
    WHERE objectID = cte.objectID
    AND indexID = cte.indexID
    AND includedColumns IS NOT NULL
    ORDER BY columnID
    FOR XML PATH(''),
    TYPE).value('.','varchar(max)'),1,1,''), '') AS includedColumns
    , COALESCE(STUFF((
    SELECT ', ' + partitionColumns
    FROM indexCTE
    WHERE objectID = cte.objectID
    AND indexID = cte.indexID
    AND partitionColumns IS NOT NULL
    ORDER BY partitionOrdinal
    FOR XML PATH(''),
    TYPE).value('.','varchar(max)'),1,1,''), '') AS partitionKeys
    , isPrimaryKey
    , isUnique
    , isUniqueConstraint
    , isFilteredIndex
    , filterDefinition
    FROM indexCTE AS cte
    --WHERE tableName = 'SalesOrderDetail'
    WHERE indexName NOT LIKE 'mtpk_%' AND indexName NOT LIKE 'mt_%'
    ORDER BY tableName
    , indexName;

  • Alexei

    Howard - you rock!

  • Jeff O

    Very nice! Thank you for this easy query to find all indexes

  • JorgeT

    Very Nice Query

  • Guest

    Fixed the annoying quotes:

    WITH indexCTE AS
    (
    SELECT st.object_id AS objectID
    , st.name AS tableName
    , si.index_id AS indexID
    , si.name AS indexName
    , si.type_desc AS indexType
    , sc.column_id AS columnID
    , sc.name + CASE WHEN sic.is_descending_key = 1 THEN 'DESC' ELSE '' END AS columnName
    , sic.key_ordinal AS ordinalPosition
    , CASE WHEN sic.is_included_column = 0 AND key_ordinal > 0 THEN sc.name ELSE NULL END AS indexKeys
    , CASE WHEN sic.is_included_column = 1 THEN sc.name ELSE NULL END AS includedColumns
    , sic.partition_ordinal AS partitionOrdinal
    , CASE WHEN sic.partition_ordinal > 0 THEN sc.name ELSE NULL END AS partitionColumns
    , si.is_primary_key AS isPrimaryKey
    , si.is_unique AS isUnique
    , si.is_unique_constraint AS isUniqueConstraint
    , si.has_filter AS isFilteredIndex
    , COALESCE(si.filter_definition, '') AS filterDefinition
    FROM sys.tables AS st
    INNER JOIN sys.indexes AS si
    ON si.object_id = st.object_id
    INNER JOIN sys.index_columns AS sic
    ON sic.object_id=si.object_id
    AND sic.index_id=si.index_id
    INNER JOIN sys.columns AS sc
    ON sc.object_id = sic.object_id
    and sc.column_id = sic.column_id
    )

    SELECT DISTINCT
    @@SERVERNAME AS ServerName
    , DB_NAME() AS DatabaseName
    , tableName
    , indexName
    , indexType
    , STUFF((
    SELECT ', ' + indexKeys
    FROM indexCTE
    WHERE objectID = cte.objectID
    AND indexID = cte.indexID
    AND indexKeys IS NOT NULL
    ORDER BY ordinalPosition
    FOR XML PATH(''),
    TYPE).value('.','varchar(max)'),1,1,'') AS indexKeys

    , COALESCE(STUFF((
    SELECT ', ' + includedColumns
    FROM indexCTE
    WHERE objectID = cte.objectID
    AND indexID = cte.indexID
    AND includedColumns IS NOT NULL
    ORDER BY columnID
    FOR XML PATH(''),
    TYPE).value('.','varchar(max)'),1,1,''), '') AS includedColumns
    , COALESCE(STUFF((
    SELECT ', ' + partitionColumns
    FROM indexCTE
    WHERE objectID = cte.objectID
    AND indexID = cte.indexID
    AND partitionColumns IS NOT NULL
    ORDER BY partitionOrdinal
    FOR XML PATH(''),
    TYPE).value('.','varchar(max)'),1,1,''), '') AS partitionKeys
    , isPrimaryKey
    , isUnique
    , isUniqueConstraint
    , isFilteredIndex
    , filterDefinition
    FROM indexCTE AS cte
    -- WHERE tableName = 'SalesOrderDetail'
    WHERE indexName NOT LIKE 'mtpk_%' AND indexName NOT LIKE 'mt_%'
    ORDER BY tableName
    , indexName;

  • Guest

    Well, I tried, looks like this website is changing those quotes.

  • Naveen

    This is really awesome. It worked like a Charm for me

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">