SQL Server Performance

how do i list all indexes with all columns and data types on a database?

Discussion in 'SQL Server 2005 General DBA Questions' started by Trev256b, Oct 9, 2011.

  1. Trev256b Member

    i want to list all the columns (and data types) used in all indexes in a database - so i can view and analyse these easily. 1) how can this be done - is there an easy dmv?
    Trev256b, Oct 9, 2011
    #1

  2. Luis Martin Moderator

    There is a lot of documentation software.
    My suggestion is Red Gate.
    Luis Martin, Oct 9, 2011
    #2

  3. Trev256b Member

    thanks - i've read a lot of documentation and blogs but strangely nobody seems to have code that can neatly list all indexes and columns for one database. red gate appeasr to be software and not documentation - did you get xconfused or are you suggesting using red gate to develop code to do this? if so why as you mentioned there is documentation. please send any handy links if you have then as i can't find any. either way luis to let you know i used a couple of sp's to work it out but for some reason nobody has worked out how to do this...
    Trev256b, Oct 9, 2011
    #3

  4. Luis Martin Moderator

    Luis Martin, Oct 9, 2011
    #4

  5. FrankKalis Moderator

    Well, it is all there for the taking. Sysindexes, sysindexkeys, etc...
    I'm pretty sure there are scripts for this already out there as well. ;)
    FrankKalis, Oct 11, 2011
    #5

  6. satya Moderator

    It is possible to do with simple TSQL querying against system tables, the 2 system catalogs are sys.indexes and sys.index_columns.
    See this query which I got it from web (dont know the resource):
    SELECT Tab.[name] AS TableName,
    IND.[name] AS IndexName,
    SUBSTRING(( SELECT ', ' + AC.name
    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]
    AND I.[index_id] = IC.[index_id]
    INNER JOIN sys.[all_columns] AC
    ON T.[object_id] = AC.[object_id]
    AND IC.[column_id] = AC.[column_id]
    WHERE Ind.[object_id] = i.[object_id]
    AND ind.index_id = i.index_id
    ORDER BY IC.key_ordinal
    FOR
    XML PATH('')
    ), 2, 8000) AS COLS
    FROM sys.[indexes] Ind
    INNER JOIN sys.[tables] AS Tab
    ON Tab.[object_id] = Ind.[object_id]
    ORDER BY TableName
    satya, Oct 17, 2011
    #6

Share This Page