Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

DbIndexes View - SQL Server

RSS
Modified on Thu, May 11, 2023, 8:06 AM by Administrator Categorized as (Favorites), Code Sample, SQL Server

Overview

  • This database view lists out all indexes in the database.
  • The ColumnName field lists indexed columns in their key order. Columns included in the index but not the key (i.e., in a covering index) are enclosed in parentheses.

Code

/*==================================================================================================
    OBJECT: DbIndexes view
    SOURCE: http://jasinskionline.com/technicalwiki/DbIndexes-View-SQL-Server.ashx
==================================================================================================*/
create or alter view dbo.DbIndexes as

select
     TableName      = o.name
    ,IndexName      = idx.Name
    ,ColumnNames    = string_agg(
                            case when ic.is_included_column = 1 then '(' else '' end
                            + c.name
                            + case when ic.is_included_column = 1 then ')' else '' end
                            , ','
                            ) within group (order by ic.is_included_column, ic.key_ordinal)
from
    sys.indexes idx
    inner join sys.tables o on idx.object_id = o.object_id
    inner join sys.index_columns ic on idx.index_id = ic.index_id and idx.object_id = ic.object_id
    inner join sys.columns c on ic.column_id = c.column_id and c.object_id = o.object_id
group by
    o.name
    ,idx.name

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.