indice - Lista de todas las columnas de índice e índice en SQL Server DB

indices clustered y nonclustered sql server

Question

¿Cómo obtengo una lista de todas las columnas de índice e índice en SQL Server 2005+? Lo más cerca que pude conseguir es:

select s.name, t.name, i.name, c.name from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
inner join sys.columns c on c.object_id = t.object_id and
        ic.column_id = c.column_id

where i.index_id > 0    
 and i.type in (1, 2) -- clustered & nonclustered only
 and i.is_primary_key = 0 -- do not include PK indexes
 and i.is_unique_constraint = 0 -- do not include UQ
 and i.is_disabled = 0
 and i.is_hypothetical = 0
 and ic.key_ordinal > 0

order by ic.key_ordinal

Que no es exactamente lo que quiero.
Lo que quiero es, enumerar todos los índices definidos por el usuario, (lo que significa que no hay índices que admitan restricciones y claves primarias únicas ) con todas las columnas (ordenadas por cómo aparecen en la definición del índice) más la mayor cantidad de metadatos posible.

Answer 1

¿Puedo aventurar otra respuesta a esta pregunta saturada?

Esta es una modificación progresiva de la respuesta de @marc_s, combinada con algunas cosas de @Tim Ford, con el objetivo de tener un conjunto de resultados y una visualización final más limpios y sencillos, y realizar el pedido para mi necesidad actual.

SELECT 
    OBJECT_SCHEMA_NAME(t.[object_id],DB_ID()) AS [Schema],
    t.[name] AS [TableName], 
    ind.[name] AS [IndexName], 
    col.[name] AS [ColumnName],
    ic.column_id AS [ColumnId],
    ind.[type_desc] AS [IndexTypeDesc], 
    col.is_identity AS [IsIdentity],
    ind.[is_unique] AS [IsUnique],
    ind.[is_primary_key] AS [IsPrimaryKey],
    ic.[is_descending_key] AS [IsDescendingKey],
    ic.[is_included_column] AS [IsIncludedColumn]
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
    --ind.is_primary_key = 1 -- include or not pks, etc
    --AND ind.is_unique = 0
    --AND ind.is_unique_constraint = 0 
ORDER BY 
    [Schema],
    TableName, 
    IndexName,
    [ColumnId],
    ColumnName

Answer 2

--Corto y dulce:

SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],  
  T.[name] AS [table_name], I.[name] AS [index_name], AC.[name] AS [column_name],  
  I.[type_desc], I.[is_unique], I.[data_space_id], I.[ignore_dup_key], I.[is_primary_key], 
  I.[is_unique_constraint], I.[fill_factor],    I.[is_padded], I.[is_disabled], I.[is_hypothetical], 
  I.[allow_row_locks], I.[allow_page_locks], IC.[is_descending_key], IC.[is_included_column] 
FROM sys.[tables] AS T  
  INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]  
  INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] 
  INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id] 
WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> ''HEAP'' 
ORDER BY T.[name], I.[index_id], IC.[key_ordinal]   

Answer 3

Basado en el código de Tim Ford, esta es la respuesta correcta:

  select tab.[name]  as [table_name],
         idx.[name]  as [index_name],
         allc.[name] as [column_name],
         idx.[type_desc],
         idx.[is_unique],
         idx.[data_space_id],
         idx.[ignore_dup_key],
         idx.[is_primary_key],
         idx.[is_unique_constraint],
         idx.[fill_factor],
         idx.[is_padded],
         idx.[is_disabled],
         idx.[is_hypothetical],
         idx.[allow_row_locks],
         idx.[allow_page_locks],
         idxc.[is_descending_key],
         idxc.[is_included_column],
         idxc.[index_column_id]

     from sys.[tables] as tab

    inner join sys.[indexes]       idx  on tab.[object_id] =  idx.[object_id]
    inner join sys.[index_columns] idxc on idx.[object_id] = idxc.[object_id] and  idx.[index_id]  = idxc.[index_id]
    inner join sys.[all_columns]   allc on tab.[object_id] = allc.[object_id] and idxc.[column_id] = allc.[column_id]

    where tab.[name] Like ''%table_name%''
      and idx.[name] Like ''%index_name%''
    order by tab.[name], idx.[index_id], idxc.[index_column_id]

Answer 4

En oráculo

select CONNECYBY.SCHEMA_NAME,CONNECYBY.TABLE_NAME,CONNECYBY.INDEX_NAME,CONNECYBY.COLUMN_NAME
from (  select TABLE_OWNER SCHEMA_NAME,TABLE_NAME,INDEX_NAME,COLUMN_POSITION,trim('','' from sys_connect_by_path(COLUMN_NAME,'','')) COLUMN_NAME
        from DBA_IND_COLUMNS
        start with COLUMN_POSITION = 1
        connect by TABLE_OWNER = prior TABLE_OWNER
        and TABLE_NAME = prior TABLE_NAME
        and INDEX_NAME = prior INDEX_NAME
        and COLUMN_POSITION = prior COLUMN_POSITION + 1) CONNECYBY
join (  select TABLE_OWNER SCHEMA_NAME,TABLE_NAME,INDEX_NAME,max(COLUMN_POSITION) COLUMN_POSITION
        from DBA_IND_COLUMNS
        group by TABLE_OWNER,TABLE_NAME,INDEX_NAME) MAX_CONNECYBY
on (    CONNECYBY.SCHEMA_NAME = MAX_CONNECYBY.SCHEMA_NAME
        and CONNECYBY.TABLE_NAME = MAX_CONNECYBY.TABLE_NAME
        and CONNECYBY.INDEX_NAME = MAX_CONNECYBY.INDEX_NAME
        and CONNECYBY.COLUMN_POSITION = MAX_CONNECYBY.COLUMN_POSITION)
order by CONNECYBY.SCHEMA_NAME,CONNECYBY.TABLE_NAME,CONNECYBY.INDEX_NAME

En SQL Server con

CONNECTBY(SCHEMA_NAME,TABLE_NAME,INDEX_NAME,INDEX_COLUMN_ID,COLUMN_NAME) 
as 
    (   select SCHEMAS.NAME SCHEMA_NAME
            , TABLES.NAME TABLE_NAME
            , INDEXES.NAME INDEX_NAME
            , INDEX_COLUMNS.INDEX_COLUMN_ID INDEX_COLUMN_ID
            , cast(COLUMNS.NAME AS VARCHAR(MAX)) COLUMN_NAME
        from SYS.INDEXES
        join SYS.TABLES on (INDEXES.OBJECT_ID = TABLES.OBJECT_ID)
        join SYS.SCHEMAS on (TABLES.SCHEMA_ID = SCHEMAS.SCHEMA_ID)
        join SYS.INDEX_COLUMNS on ( INDEXES.OBJECT_ID = INDEX_COLUMNS.OBJECT_ID 
                                    and INDEX_COLUMNS.INDEX_ID = INDEXES.INDEX_ID)
        join SYS.COLUMNS on (   INDEXES.OBJECT_ID = COLUMNS.OBJECT_ID 
                                and INDEX_COLUMNS.COLUMN_ID = COLUMNS.COLUMN_ID)
        where INDEX_COLUMNS.INDEX_COLUMN_ID = 1
        union all
        select SCHEMAS.NAME SCHEMA_NAME
            , TABLES.NAME TABLE_NAME
            , INDEXES.NAME INDEX_NAME
            , INDEX_COLUMNS.INDEX_COLUMN_ID INDEX_COLUMN_ID
            , cast(PRIOR.COLUMN_NAME + '','' + COLUMNS.NAME AS VARCHAR(MAX)) COLUMN_NAME
        from SYS.INDEXES
        join SYS.TABLES on (INDEXES.OBJECT_ID = TABLES.OBJECT_ID)
        join SYS.SCHEMAS on (TABLES.SCHEMA_ID = SCHEMAS.SCHEMA_ID)
        join SYS.INDEX_COLUMNS on ( INDEXES.OBJECT_ID = INDEX_COLUMNS.OBJECT_ID 
                                    and INDEX_COLUMNS.INDEX_ID = INDEXES.INDEX_ID)
        join SYS.COLUMNS on (   INDEXES.OBJECT_ID = COLUMNS.OBJECT_ID 
                                and INDEX_COLUMNS.COLUMN_ID = COLUMNS.COLUMN_ID)
        join CONNECTBY as PRIOR on (SCHEMAS.NAME = PRIOR.SCHEMA_NAME 
                                    and TABLES.NAME = PRIOR.TABLE_NAME 
                                    and INDEXES.NAME = PRIOR.INDEX_NAME 
                                    and INDEX_COLUMNS.INDEX_COLUMN_ID = PRIOR.INDEX_COLUMN_ID + 1))
select CONNECTBY.SCHEMA_NAME,CONNECTBY.TABLE_NAME,CONNECTBY.INDEX_NAME,CONNECTBY.COLUMN_NAME
from CONNECTBY
join (  select  SCHEMA_NAME
                , TABLE_NAME
                , INDEX_NAME
                , MAX(INDEX_COLUMN_ID) INDEX_COLUMN_ID
        from CONNECTBY 
        group by SCHEMA_NAME,TABLE_NAME,INDEX_NAME) MAX_CONNECTBY
        on (CONNECTBY.SCHEMA_NAME = MAX_CONNECTBY.SCHEMA_NAME
            and CONNECTBY.TABLE_NAME = MAX_CONNECTBY.TABLE_NAME
            and CONNECTBY.INDEX_NAME = MAX_CONNECTBY.INDEX_NAME
            and CONNECTBY.INDEX_COLUMN_ID = MAX_CONNECTBY.INDEX_COLUMN_ID)
order by CONNECTBY.SCHEMA_NAME,CONNECTBY.TABLE_NAME,CONNECTBY.INDEX_NAME

Answer 5

Esta es una forma de retroceder en los índices. Puede utilizar SHOWCONTIG para evaluar la fragmentación. Enumera todos los índices de la base de datos o tabla, junto con las estadísticas. Me gustaría advertir que en una base de datos grande, puede ser de larga duración. Para mí, uno de los beneficios de este enfoque es que no tienes que ser un administrador para usarlo.

- Muestra información de fragmentación en todos los índices en una base de datos.

SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG WITH ALL_INDEXES
GO

... desactiva NOCOUNT cuando termine

- Muestra información de fragmentación en todos los índices en una tabla

SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG (authors) WITH ALL_INDEXES
GO

- Mostrar información de fragmentación en un índice específico

SET NOCOUNT ON
USE pubs
DBCC SHOWCONTIG (authors,aunmind)
GO

Answer 6

Hay dos vistas de catálogo "sys" que puedes consultar:

select * from sys.indexes

select * from sys.index_columns

Esos le darán casi cualquier información que pueda desear sobre los índices y sus columnas.

EDIT: esta consulta se está acercando bastante a lo que estás buscando:

SELECT 
     TableName = t.name,
     IndexName = ind.name,
     IndexId = ind.index_id,
     ColumnId = ic.index_column_id,
     ColumnName = col.name,
     ind.*,
     ic.*,
     col.* 
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 
     ind.is_primary_key = 0 
     AND ind.is_unique = 0 
     AND ind.is_unique_constraint = 0 
     AND t.is_ms_shipped = 0 
ORDER BY 
     t.name, ind.name, ind.index_id, ic.index_column_id;

Answer 7

He necesitado obtener índices particulares, sus columnas de índice y sus columnas incluidas también. Aquí está la consulta que he usado:

SELECT INX.[name] AS [Index Name]
      ,TBL.[name] AS [Table Name]
      ,DS1.[IndexColumnsNames]
      ,DS2.[IncludedColumnsNames]
FROM [sys].[indexes] INX
INNER JOIN [sys].[tables] TBL
    ON INX.[object_id] = TBL.[object_id]
CROSS APPLY 
(
    SELECT STUFF
    (
        (
            SELECT '' ['' + CLS.[name] + '']''
            FROM [sys].[index_columns] INXCLS
            INNER JOIN [sys].[columns] CLS 
                ON INXCLS.[object_id] = CLS.[object_id] 
                AND INXCLS.[column_id] = CLS.[column_id]
            WHERE INX.[object_id] = INXCLS.[object_id] 
                AND INX.[index_id] = INXCLS.[index_id]
                AND INXCLS.[is_included_column] = 0
            FOR XML PATH('''')
        )
        ,1
        ,1
        ,''''
    ) 
) DS1 ([IndexColumnsNames])
CROSS APPLY 
(
    SELECT STUFF
    (
        (
            SELECT '' ['' + CLS.[name] + '']''
            FROM [sys].[index_columns] INXCLS
            INNER JOIN [sys].[columns] CLS 
                ON INXCLS.[object_id] = CLS.[object_id] 
                AND INXCLS.[column_id] = CLS.[column_id]
            WHERE INX.[object_id] = INXCLS.[object_id] 
                AND INX.[index_id] = INXCLS.[index_id]
                AND INXCLS.[is_included_column] = 1
            FOR XML PATH('''')
        )
        ,1
        ,1
        ,''''
    ) 
) DS2 ([IncludedColumnsNames])

Answer 8

Hola chicos, no pasé, pero obtuve lo que quería en la consulta publicada por el autor original.

Lo usé (sin condiciones / filtros) para mi requerimiento pero dio resultados incorrectos

El principal problema fue el resultado de obtener un producto cruzado sin condición de unión en index_id

SELECT S.NAME SCHEMA_NAME,T.NAME TABLE_NAME,I.NAME INDEX_NAME,C.NAME COLUMN_NAME
  FROM SYS.TABLES T
       INNER JOIN SYS.SCHEMAS S
    ON T.SCHEMA_ID = S.SCHEMA_ID
       INNER JOIN SYS.INDEXES I
    ON I.OBJECT_ID = T.OBJECT_ID
       INNER JOIN SYS.INDEX_COLUMNS IC
    ON IC.OBJECT_ID = T.OBJECT_ID
       INNER JOIN SYS.COLUMNS C
    ON C.OBJECT_ID  = T.OBJECT_ID
   **AND IC.INDEX_ID    = I.INDEX_ID**
   AND IC.COLUMN_ID = C.COLUMN_ID
 WHERE 1=1

ORDER BY I.NAME,I.INDEX_ID,IC.KEY_ORDINAL

Answer 9

La siguiente consulta incluye toda la información pertinente para los índices definidos por el usuario, (sin índices para restricciones únicas y claves primarias) con todas las columnas:

SELECT I.name as IndexName, 
        CASE WHEN I.is_unique = 1 THEN ''Yes'' ELSE ''No'' END as ''Unique'',
        I.type_desc COLLATE DATABASE_DEFAULT as Index_Type,
        ''['' + SCHEMA_NAME(T.schema_id) + '']'' as ''Schema'',
        ''['' + T.name + '']'' as TableName,
        STUFF((SELECT '', ['' + C.name + CASE WHEN IC.is_descending_key = 0 THEN ''] ASC'' ELSE ''] DESC'' END
            FROM sys.index_columns IC INNER JOIN sys.columns C ON  IC.object_id = C.object_id  AND IC.column_id = C.column_id
            WHERE IC.is_included_column = 0 AND IC.object_id = I.object_id AND IC.index_id = I.Index_id
            FOR XML PATH('''')), 1, 2, '''') as Key_Columns,
        Included_Columns, 
        I.filter_definition,
        CASE WHEN I.is_padded = 1 THEN ''ON'' ELSE ''OFF'' END as PAD_INDEX, 
        CASE WHEN ST.no_recompute = 0 THEN ''OFF'' ELSE ''ON'' END as [Statistics_Norecompute],
        CONVERT(VARCHAR(5), CASE WHEN I.fill_factor = 0 THEN 100 ELSE I.fill_factor END) as [Fillfactor],
        CASE WHEN I.ignore_dup_key = 1 THEN ''ON'' ELSE ''OFF'' END as [Ignore_Dup_Key],       
        CASE WHEN I.allow_row_locks = 1 THEN ''ON'' ELSE ''OFF'' END as [Allow_Row_Locks], 
        CASE WHEN I.allow_page_locks = 1 THEN ''ON'' ELSE ''OFF'' END [Allow_Page_Locks]        
FROM    sys.indexes I INNER JOIN        
        sys.tables T ON  T.object_id = I.object_id INNER JOIN       
        sys.stats ST ON  ST.object_id = I.object_id AND ST.stats_id = I.index_id INNER JOIN 
        sys.data_spaces DS ON  I.data_space_id = DS.data_space_id INNER JOIN 
        sys.filegroups FG ON  I.data_space_id = FG.data_space_id LEFT OUTER JOIN 
        (SELECT * FROM 
            (SELECT IC2.object_id, IC2.index_id,
                STUFF((SELECT '', '' + C.name FROM sys.index_columns IC1 INNER JOIN 
                    sys.columns C ON C.object_id = IC1.object_id
                        AND C.column_id = IC1.column_id
                        AND IC1.is_included_column = 1
                    WHERE  IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id
                    GROUP BY IC1.object_id, C.name, index_id  FOR XML PATH('''')
                ), 1, 2, '''') as Included_Columns
            FROM sys.index_columns IC2
            GROUP BY IC2.object_id, IC2.index_id) tmp1
            WHERE Included_Columns IS NOT NULL
        ) tmp2
        ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id
WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0;

Como un bono adicional, la consulta a continuación está formateada para escribir los scripts de creación de índice y caída de índice:

SELECT I.name as IndexName, 
        -- Uncommnent line below to include checking for index exists as part of the script
        --''IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = ''''''+ I.name +'''''') '' +
        ''CREATE '' + CASE WHEN I.is_unique = 1 THEN '' UNIQUE '' ELSE '''' END +
        I.type_desc COLLATE DATABASE_DEFAULT + '' INDEX ['' +
        I.name + ''] ON ['' + SCHEMA_NAME(T.schema_id) + ''].['' + T.name + ''] ('' + STUFF(
        (SELECT '', ['' + C.name + CASE WHEN IC.is_descending_key = 0 THEN ''] ASC'' ELSE ''] DESC'' END
            FROM sys.index_columns IC INNER JOIN sys.columns C ON  IC.object_id = C.object_id  AND IC.column_id = C.column_id
            WHERE IC.is_included_column = 0 AND IC.object_id = I.object_id AND IC.index_id = I.Index_id
            FOR XML PATH('''')), 1, 2, '''')  + '') '' +
        ISNULL('' INCLUDE ('' + IncludedColumns + '') '', '''') +
        ISNULL('' WHERE '' + I.filter_definition, '''') + 
        ''WITH (PAD_INDEX = '' + CASE WHEN I.is_padded = 1 THEN ''ON'' ELSE ''OFF'' END + 
        '', STATISTICS_NORECOMPUTE = '' + CASE WHEN ST.no_recompute = 0 THEN ''OFF'' ELSE ''ON'' END + 
        '', SORT_IN_TEMPDB = OFF'' + 
        '', FILLFACTOR = '' + CONVERT(VARCHAR(5), CASE WHEN I.fill_factor = 0 THEN 100 ELSE I.fill_factor END) +
        '', IGNORE_DUP_KEY = '' + CASE WHEN I.ignore_dup_key = 1 THEN ''ON'' ELSE ''OFF'' END +      
        '', ONLINE = OFF'' + 
        '', ALLOW_ROW_LOCKS = '' + CASE WHEN I.allow_row_locks = 1 THEN ''ON'' ELSE ''OFF'' END + 
        '', ALLOW_PAGE_LOCKS = '' + CASE WHEN I.allow_page_locks = 1 THEN ''ON'' ELSE ''OFF'' END + 
        '') ON ['' + DS.name + ''];'' + CHAR(13) + CHAR(10) + ''GO'' as [CreateIndex],
        ''DROP INDEX [''+ I.name +''] ON [''+ SCHEMA_NAME(T.schema_id) +''].[''+ T.name +''];'' +
        CHAR(13) + CHAR(10) + ''GO'' AS [DropIndex]
FROM    sys.indexes I INNER JOIN        
        sys.tables T ON  T.object_id = I.object_id INNER JOIN       
        sys.stats ST ON  ST.object_id = I.object_id AND ST.stats_id = I.index_id INNER JOIN 
        sys.data_spaces DS ON  I.data_space_id = DS.data_space_id INNER JOIN 
        sys.filegroups FG ON  I.data_space_id = FG.data_space_id LEFT OUTER JOIN 
        (SELECT * FROM 
            (SELECT IC2.object_id, IC2.index_id,
                STUFF((SELECT '', '' + C.name FROM sys.index_columns IC1 INNER JOIN 
                    sys.columns C ON C.object_id = IC1.object_id
                        AND C.column_id = IC1.column_id
                        AND IC1.is_included_column = 1
                    WHERE  IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id
                    GROUP BY IC1.object_id, C.name, index_id  FOR XML PATH('''')
                ), 1, 2, '''') as IncludedColumns
            FROM sys.index_columns IC2
            GROUP BY IC2.object_id, IC2.index_id) tmp1
            WHERE IncludedColumns IS NOT NULL
        ) tmp2
        ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id
WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0 

Answer 10

La solución anterior es elegante, pero según MS, INDEXKEY_PROPERTY está en desuso. Consulte: msdn.microsoft.com/en-us/library/ms186773.aspx

Answer 11

Lo siguiente da lo que es similar a sp_helpindex tablename

select T.name as TableName, I.name as IndexName, AC.Name as ColumnName, I.type_desc as IndexType 
from sys.tables as T inner join sys.indexes as I on T.[object_id] = I.[object_id] 
   inner join sys.index_columns as IC on IC.[object_id] = I.[object_id] and IC.[index_id] = I.[index_id] 
   inner join sys.all_columns as AC on IC.[object_id] = AC.[object_id] and IC.[column_id] = AC.[column_id] 
order by T.name, I.name

Answer 12

Lo siguiente funciona en SQL Server 2014/2016, así como en cualquier base de datos SQL de Microsoft Azure.

Produce un conjunto de resultados completo que es fácilmente exportable a Notepad / Excel para cortar y cortar en cubitos e incluye

1. Nombre de la tabla
2. Nombre del índice
3. Descripción del índice
4. Columnas indexadas - en orden
5. Columnas incluidas - en orden

 SELECT ''['' + s.NAME + ''].['' + o.NAME + '']'' AS ''table_name''
    ,+ i.NAME AS ''index_name''
    ,LOWER(i.type_desc) + CASE 
        WHEN i.is_unique = 1
            THEN '', unique''
        ELSE ''''
        END + CASE 
        WHEN i.is_primary_key = 1
            THEN '', primary key''
        ELSE ''''
        END AS ''index_description''
    ,STUFF((
            SELECT '', ['' + sc.NAME + '']'' AS "text()"
            FROM syscolumns AS sc
            INNER JOIN sys.index_columns AS ic ON ic.object_id = sc.id
                AND ic.column_id = sc.colid
            WHERE sc.id = so.object_id
                AND ic.index_id = i1.indid
                AND ic.is_included_column = 0
            ORDER BY key_ordinal
            FOR XML PATH('''')
            ), 1, 2, '''') AS ''indexed_columns''
    ,STUFF((
            SELECT '', ['' + sc.NAME + '']'' AS "text()"
            FROM syscolumns AS sc
            INNER JOIN sys.index_columns AS ic ON ic.object_id = sc.id
                AND ic.column_id = sc.colid
            WHERE sc.id = so.object_id
                AND ic.index_id = i1.indid
                AND ic.is_included_column = 1
            FOR XML PATH('''')
            ), 1, 2, '''') AS ''included_columns''
FROM sysindexes AS i1
INNER JOIN sys.indexes AS i ON i.object_id = i1.id
    AND i.index_id = i1.indid
INNER JOIN sysobjects AS o ON o.id = i1.id
INNER JOIN sys.objects AS so ON so.object_id = o.id
    AND is_ms_shipped = 0
INNER JOIN sys.schemas AS s ON s.schema_id = so.schema_id
WHERE so.type = ''U''
    AND i1.indid < 255
    AND i1.STATUS & 64 = 0 --index with duplicates
    AND i1.STATUS & 8388608 = 0 --auto created index
    AND i1.STATUS & 16777216 = 0 --stats no recompute
    AND i.type_desc <> ''heap''
    AND so.NAME <> ''sysdiagrams''
ORDER BY table_name
    ,index_name;

Answer 13

Ninguno de los anteriores hizo el trabajo por mí, pero esto hace:

-- KDF9''s concise index list for SQL Server 2005+  (see below for 2000)
--   includes schemas and primary keys, in easy to read format
--   with unique, clustered, and all ascending/descendings in a single column
-- Needs simple manual add or delete to change maximum number of key columns
--   but is easy to understand and modify, with no UDFs or complex logic
--
SELECT
  schema_name(schema_id) as SchemaName, OBJECT_NAME(si.object_id) as TableName, si.name as IndexName,
  (CASE is_primary_key WHEN 1 THEN ''PK'' ELSE '''' END) as PK,
  (CASE is_unique WHEN 1 THEN ''1'' ELSE ''0'' END)+'' ''+
  (CASE si.type WHEN 1 THEN ''C'' WHEN 3 THEN ''X'' ELSE ''B'' END)+'' ''+  -- B=basic, C=Clustered, X=XML
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,1,''IsDescending'') WHEN 0 THEN ''A'' WHEN 1 THEN ''D'' ELSE '''' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,2,''IsDescending'') WHEN 0 THEN ''A'' WHEN 1 THEN ''D'' ELSE '''' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,3,''IsDescending'') WHEN 0 THEN ''A'' WHEN 1 THEN ''D'' ELSE '''' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,4,''IsDescending'') WHEN 0 THEN ''A'' WHEN 1 THEN ''D'' ELSE '''' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,5,''IsDescending'') WHEN 0 THEN ''A'' WHEN 1 THEN ''D'' ELSE '''' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,6,''IsDescending'') WHEN 0 THEN ''A'' WHEN 1 THEN ''D'' ELSE '''' END)+
  '''' as ''Type'',
  INDEX_COL(schema_name(schema_id)+''.''+OBJECT_NAME(si.object_id),index_id,1) as Key1,
  INDEX_COL(schema_name(schema_id)+''.''+OBJECT_NAME(si.object_id),index_id,2) as Key2,
  INDEX_COL(schema_name(schema_id)+''.''+OBJECT_NAME(si.object_id),index_id,3) as Key3,
  INDEX_COL(schema_name(schema_id)+''.''+OBJECT_NAME(si.object_id),index_id,4) as Key4,
  INDEX_COL(schema_name(schema_id)+''.''+OBJECT_NAME(si.object_id),index_id,5) as Key5,
  INDEX_COL(schema_name(schema_id)+''.''+OBJECT_NAME(si.object_id),index_id,6) as Key6
FROM sys.indexes as si
LEFT JOIN sys.objects as so on so.object_id=si.object_id
WHERE index_id>0 -- omit the default heap
  and OBJECTPROPERTY(si.object_id,''IsMsShipped'')=0 -- omit system tables
  and not (schema_name(schema_id)=''dbo'' and OBJECT_NAME(si.object_id)=''sysdiagrams'') -- omit sysdiagrams
ORDER BY SchemaName,TableName,IndexName

-------------------------------------------------------------------
-- or to generate creation scripts put a simple wrapper around that
SELECT SchemaName, TableName, IndexName,
  (CASE pk
    WHEN ''PK'' THEN ''ALTER ''+
     ''TABLE ''+SchemaName+''.''+TableName+'' ADD CONSTRAINT ''+IndexName+'' PRIMARY KEY''+
     (CASE substring(Type,3,1) WHEN ''C'' THEN '' CLUSTERED'' ELSE '''' END)
    ELSE ''CREATE ''+
     (CASE substring(Type,1,1) WHEN ''1'' THEN ''UNIQUE '' ELSE '''' END)+
     (CASE substring(Type,3,1) WHEN ''C'' THEN ''CLUSTERED '' ELSE '''' END)+
     ''INDEX ''+IndexName+'' ON ''+SchemaName+''.''+TableName
    END)+
  '' (''+
    (CASE WHEN Key1 is null THEN '''' ELSE      Key1+(CASE substring(Type,4+1,1) WHEN ''D'' THEN '' DESC'' ELSE '''' END) END)+
    (CASE WHEN Key2 is null THEN '''' ELSE '', ''+Key2+(CASE substring(Type,4+2,1) WHEN ''D'' THEN '' DESC'' ELSE '''' END) END)+
    (CASE WHEN Key3 is null THEN '''' ELSE '', ''+Key3+(CASE substring(Type,4+3,1) WHEN ''D'' THEN '' DESC'' ELSE '''' END) END)+
    (CASE WHEN Key4 is null THEN '''' ELSE '', ''+Key4+(CASE substring(Type,4+4,1) WHEN ''D'' THEN '' DESC'' ELSE '''' END) END)+
    (CASE WHEN Key5 is null THEN '''' ELSE '', ''+Key5+(CASE substring(Type,4+5,1) WHEN ''D'' THEN '' DESC'' ELSE '''' END) END)+
    (CASE WHEN Key6 is null THEN '''' ELSE '', ''+Key6+(CASE substring(Type,4+6,1) WHEN ''D'' THEN '' DESC'' ELSE '''' END) END)+
    '')'' as CreateIndex
FROM (
  ...
  ...listing SQL same as above minus the ORDER BY...
  ...
  ) as indexes
ORDER BY SchemaName,TableName,IndexName

----------------------------------------------------------
-- For SQL Server 2000 the following should work
--   change table names to sysindexes and sysobjects (no dots)
--   change object_id => id, index_id => indid,
--   change is_primary_key => (select count(constid) from sysconstraints as sc where sc.id=si.id and sc.status&15=1)
--   change is_unique => INDEXPROPERTY(si.id,si.name,''IsUnique'')
--   change si.type => INDEXPROPERTY(si.id,si.name,''IsClustered'')
--   remove all references to schemas including schema name qualifiers, and the XML type
--   add select where indid<255 and si.status&64=0 (to omit the text/image index and autostats)

Si sus nombres incluyen espacios, agregue corchetes alrededor de ellos en los scripts de creación.

Cuando la última columna de la clave es nula, sabes que no falta ninguna.

Filtrar las claves primarias, etc. como en la solicitud original es trivial.

NOTA: Tenga cuidado con esta solución, ya que no distingue las columnas indexadas e incluidas.

Answer 14

Para columnas únicas por índice:

select s.name, t.name, i.name, i.index_id,c.name,c.column_id
 from sys.schemas s
inner join sys.tables t on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
    and ic.index_id=i.index_id
inner join sys.columns c on c.object_id = t.object_id 
    and ic.column_id = c.column_id
where i.object_id = object_id(''previous.account_1'')  
order by index_id,column_id

Answer 15

Primero, tenga en cuenta que todas las consultas anteriores pueden omitirse o incorporar erróneamente las columnas INCLUDE de los índices. También falta en algunos el orden correcto y / o la opción ASC / DESC de las columnas.

Modificado la consulta anterior por jona. Además, en muchas de las bases de datos que uso, instalo mi propia función de agregado CLR CONCATENATE, por lo que el código siguiente depende de que algo así esté presente. Las declaraciones de SQL anteriores se reducen a mucho más mantenibles:

SELECT
  s.[name] AS [schema_name]
, t.[name] AS [table_name]
, i.[name] AS [index_name]
, dbo.Concatenate(CASE WHEN ic.[key_ordinal] > 0 AND ic.[is_descending_key] = 1 THEN c.[name] + '' DESC'' WHEN key_ordinal > 0 THEN c.[name] ELSE NULL END,'','',1) AS [columns]
, dbo.Concatenate(CASE WHEN ic.[is_included_column] = 1 THEN c.[name] ELSE NULL END,'','',1) AS [includes]
FROM
  sys.tables t
INNER JOIN
  sys.schemas s ON t.[schema_id] = s.[schema_id]
INNER JOIN
  sys.indexes i ON i.[object_id] = t.[object_id]
INNER JOIN
  sys.index_columns ic ON ic.[object_id] = t.[object_id] AND ic.index_id = i.index_id
INNER JOIN
  sys.columns c ON c.[object_id] = t.[object_id] AND ic.column_id = c.column_id
GROUP BY
  s.[name]
, t.[name]
, i.[name]
ORDER BY
  s.[name]
, t.[name]
, i.[name]

Existen muchos agregados de concatenación si su entorno permite que se agreguen funciones basadas en CLR.

Answer 16

Puede usar sp_helpindex para ver todos los índices de una tabla.

EXEC sys.sp_helpindex @objname = N''User'' -- nvarchar(77)

Y para todos los índices, puede atravesar sys.objects para obtener todos los índices para cada tabla.

Answer 17

Se me ocurrió esta, que me da la descripción exacta que necesito. Lo que ayuda es que se obtiene una fila por índice en el que se agregan las columnas del índice.

select 
    o.name as ObjectName, 
    i.name as IndexName, 
    i.is_primary_key as [PrimaryKey],
    SUBSTRING(i.[type_desc],0,6) as IndexType,
    i.is_unique as [Unique],
    Columns.[Normal] as IndexColumns,
    Columns.[Included] as IncludedColumns
from sys.indexes i 
join sys.objects o on i.object_id = o.object_id
cross apply
(
    select
        substring
        (
            (
                select '', '' + co.[name]
                from sys.index_columns ic
                join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id
                where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.is_included_column = 0
                order by ic.key_ordinal
                for xml path('''')
            )
            , 3
            , 10000
        )    as [Normal]    
        , substring
        (
            (
                select '', '' + co.[name]
                from sys.index_columns ic
                join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id
                where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.is_included_column = 1
                order by ic.key_ordinal
                for xml path('''')
            )
            , 3
            , 10000
        )    as [Included]    

) Columns
where o.[type] = ''U'' --USER_TABLE
order by o.[name], i.[name], i.is_primary_key desc

Answer 18

Según la respuesta aceptada y otras dos preguntas ^{1 , 2} , he reunido la siguiente consulta:

SELECT
    sys.indexes.name AS IndexName,
    sys.tables.name AS TableName,
    REPLACE((
        SELECT sys.columns.name + CASE WHEN is_descending_key = 1 THEN '' DESC'' ELSE '''' END AS [data()]
        FROM sys.index_columns
        INNER JOIN sys.columns ON sys.index_columns.object_id = sys.columns.object_id AND sys.index_columns.column_id = sys.columns.column_id
        WHERE sys.index_columns.object_id = sys.indexes.object_id AND sys.index_columns.index_id = sys.indexes.index_id AND sys.index_columns.is_included_column = 0
        ORDER BY sys.index_columns.key_ordinal
        FOR XML PATH('''')
    ), '' '', '', '') AS KeyColumns,
    REPLACE((
        SELECT sys.columns.name AS [data()]
        FROM sys.index_columns
        INNER JOIN sys.columns ON sys.index_columns.object_id = sys.columns.object_id AND sys.index_columns.column_id = sys.columns.column_id
        WHERE sys.index_columns.object_id = sys.indexes.object_id AND sys.index_columns.index_id = sys.indexes.index_id AND sys.index_columns.is_included_column = 1
        ORDER BY sys.index_columns.index_column_id
        FOR XML PATH('''')
    ), '' '', '', '') AS IncludedColumns,
    sys.dm_db_index_usage_stats.user_updates,
    sys.dm_db_index_usage_stats.user_seeks,
    sys.dm_db_index_usage_stats.user_scans,
    sys.dm_db_index_usage_stats.user_lookups,
    sys.dm_db_index_usage_stats.user_seeks + sys.dm_db_index_usage_stats.user_scans + sys.dm_db_index_usage_stats.user_lookups AS total_usage
FROM sys.indexes
LEFT JOIN sys.tables ON sys.indexes.object_id = sys.tables.object_id
LEFT JOIN sys.dm_db_index_usage_stats ON sys.indexes.object_id = sys.dm_db_index_usage_stats.object_id AND sys.indexes.index_id = sys.dm_db_index_usage_stats.index_id
WHERE sys.indexes.type <> 0 AND sys.tables.is_ms_shipped = 0

Esta consulta devuelve resultados como a continuación, que muestra la lista de índices, sus columnas y su uso. Muy útil para determinar qué índice se está desempeñando mejor que otros:

Answer 19

Solo tenga en cuenta que si va a utilizar cualquiera de las consultas anteriores para crear secuencias de comandos de sus índices, debe incorporar la columna filter_definition de la tabla sys.indexes en sus consultas para obtener la definición de filtro de índices no agrupados en SQL 2008+

A.M

Answer 20

Ya que su perfil indica que está utilizando .NET, podría usar los objetos administrados por el servidor (SMO) programáticamente ... de lo contrario, cualquiera de las respuestas anteriores es fantástica.

Answer 21

esto funcionará:

DECLARE @IndexInfo  TABLE (index_name         varchar(250)
                          ,index_description  varchar(250)
                          ,index_keys         varchar(250)
                          )

INSERT INTO @IndexInfo
exec sp_msforeachtable ''sp_helpindex ''''?''''''
select * from @IndexInfo

esto no devuelve el nombre de la tabla y recibirá advertencias para todas las tablas sin un índice; si eso es un problema, puede crear un bucle sobre las tablas que tienen índices como este:

DECLARE @IndexInfoTemp  TABLE (index_name         varchar(250)
                              ,index_description  varchar(250)
                              ,index_keys         varchar(250)
                              )

DECLARE @IndexInfo  TABLE (table_name         sysname
                          ,index_name         varchar(250)
                          ,index_description  varchar(250)
                          ,index_keys         varchar(250)
                          )

DECLARE @Tables Table (RowID       int not null identity(1,1)
                      ,TableName   sysname 
                      )
DECLARE @MaxRow       int
DECLARE @CurrentRow   int
DECLARE @CurrentTable sysname

INSERT INTO @Tables
    SELECT
        DISTINCT t.name 
        FROM sys.indexes i
            INNER JOIN sys.tables t ON i.object_id = t.object_id
        WHERE i.Name IS NOT NULL
SELECT @MaxRow=@@ROWCOUNT,@CurrentRow=1

WHILE @CurrentRow<=@MaxRow
BEGIN

    SELECT @CurrentTable=TableName FROM @Tables WHERE RowID=@CurrentRow

    INSERT INTO @IndexInfoTemp
    exec sp_helpindex @CurrentTable

    INSERT INTO @IndexInfo
            (table_name   , index_name , index_description , index_keys)
        SELECT
            @CurrentTable , index_name , index_description , index_keys
        FROM @IndexInfoTemp

    DELETE FROM @IndexInfoTemp

    SET @CurrentRow=@CurrentRow+1

END --WHILE
SELECT * from @IndexInfo

EDITAR
Si lo desea, puede filtrar los datos, aquí hay algunos ejemplos (estos funcionan para cualquiera de los métodos):

SELECT * FROM @IndexInfo WHERE index_description NOT LIKE ''%primary key%''
SELECT * FROM @IndexInfo WHERE index_description NOT LIKE ''%nonclustered%'' AND index_description  LIKE ''%clustered%''
SELECT * FROM @IndexInfo WHERE index_description LIKE ''%unique%''

Answer 22

Esta es la mejor manera de hacerlo:

SELECT sys.tables.object_id, sys.tables.name as table_name, sys.columns.name as column_name, sys.indexes.name as index_name,
sys.indexes.is_unique, sys.indexes.is_primary_key 
FROM sys.tables, sys.indexes, sys.index_columns, sys.columns 
WHERE (sys.tables.object_id = sys.indexes.object_id AND sys.tables.object_id = sys.index_columns.object_id AND sys.tables.object_id = sys.columns.object_id
AND sys.indexes.index_id = sys.index_columns.index_id AND sys.index_columns.column_id = sys.columns.column_id) 
AND sys.tables.name = ''your_table_name''

Prefiero usar uniones implícitas ya que es mucho más fácil para mi entender. Puede eliminar la referencia object_id ya que es posible que no la necesite.

Aclamaciones.

Answer 23

He utilizado la siguiente consulta cuando tuve este requisito ...

SELECT 
    TableName = t.name,
    ColumnId = col.column_id, 
    ColumnName = col.name,
    DataType = ty.name,
    MaxSize = ty.max_length,
    IsNullable = CASE WHEN (col.is_nullable = 1) THEN ''Y'' END,
    IsIdentity = CASE WHEN (col.is_identity = 1) THEN ''Y'' END,
    IsPrimaryKey = CASE WHEN (ic.column_id = col.column_id) THEN ''Y'' END,
    IsForeignKey = CASE WHEN (fkc.parent_column_id = col.column_id) THEN ''Y'' END,
    IsDefault = CASE WHEN (dc.parent_column_id = col.column_id) THEN ''Y'' END
FROM 
    sys.tables t
INNER JOIN 
     sys.columns col ON t.object_id = col.object_id 
LEFT JOIN
    sys.indexes ind ON t.object_id = ind.object_id 
LEFT JOIN 
     sys.index_columns ic ON ic.index_id=ind.index_id AND ic.object_id = col.object_id and ic.column_id = col.column_id
LEFT JOIN sys.foreign_key_columns fkc
                ON fkc.parent_object_id = col.object_id AND fkc.parent_column_id=col.column_id
LEFT JOIN sys.default_constraints dc
                ON dc.parent_object_id = col.object_id AND dc.parent_column_id=col.column_id
LEFT JOIN
     sys.types ty on ty.user_type_id = col.user_type_id

WHERE
    --t.name=''<TABLENAME>''
    t.schema_id = 10    --SCHEMA ID
    AND ind.is_primary_key=1    
ORDER BY
    t.name, ColumnId

Answer 24

Al usar SQL Server 2016, esto proporciona una lista completa de todos los índices, con un volcado incluido de cada tabla para que pueda ver cómo se relacionan las tablas. También muestra columnas incluidas en los índices de cobertura:

select t.name TableName, i.name IdxName, c.name ColName
    , ic.index_column_id ColPosition
    , i.type_desc Type
    , case when i.is_primary_key = 1 then ''Yes'' else '''' end [Primary?]
    , case when i.is_unique = 1 then ''Yes'' else '''' end [Unique?]
    , case when ic.is_included_column = 0 then '''' else ''Yes - Included'' end [CoveredColumn?]
    , ''indexes >>>>'' [*indexes*], i.*, ''index_columns >>>>'' [*index_columns*]
    , ic.*, ''tables >>>>'' [*tables*]
    , t.*, ''columns >>>>'' [*columns*], c.*
from sys.index_columns ic
join sys.tables t on t.object_id = ic.object_id
join sys.columns c on c.object_id = t.object_id and c.column_id = ic.column_id
join sys.indexes i on i.object_id = t.object_id and i.index_id = ic.index_id
order by TableName, IdxName, ColPosition

Answer 25

sELECT 
     TableName = t.name,
     IndexName = ind.name,
     --IndexId = ind.index_id,
     ColumnId = ic.index_column_id,
     ColumnName = col.name,
     key_ordinal,
     ind.type_desc
     --ind.*,
     --ic.*,
     --col.* 
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 
     ind.is_primary_key = 0 
     AND ind.is_unique = 0 
     AND ind.is_unique_constraint = 0 
     AND t.is_ms_shipped = 0 
     and t.name=''CompanyReconciliation'' --table name
     and key_ordinal>0
ORDER BY 
     t.name, ind.name, ind.index_id, ic.index_column_id 

Answer 26

with connect(schema_name,table_name,index_name,index_column_id,column_name) as
(   select s.name schema_name, t.name table_name, i.name index_name, index_column_id, cast(c.name as varchar(max)) column_name
 from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id=i.index_id
        inner join sys.columns c on c.object_id = t.object_id and
                ic.column_id = c.column_id
                where index_column_id=1
union all
select s.name schema_name, t.name table_name, i.name index_name, ic.index_column_id, cast(connect.column_name + '','' + c.name as varchar(max)) column_name
 from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id=i.index_id
        inner join sys.columns c on c.object_id = t.object_id and
                ic.column_id = c.column_id join connect on
connect.index_column_id+1 = ic.index_column_id
and connect.schema_name = s.name
and connect.table_name = t.name
and connect.index_name = i.name)
select connect.schema_name,connect.table_name,connect.index_name,connect.column_name
from connect join (select schema_name,table_name,index_name,MAX(index_column_id) index_column_id
from connect group by schema_name,table_name,index_name) mx
on connect.schema_name = mx.schema_name
and connect.table_name = mx.table_name
and connect.index_name = mx.index_name
and connect.index_column_id = mx.index_column_id
order by 1,2,3