SQL Server Performance

ANSI way of retrieving index information

Discussion in 'Performance Tuning for DBAs' started by Chappy, Jan 6, 2003.

  1. Chappy New Member

    Is there an ANSI way of retrieving a result set of Index information (IE, using INFORMATION_SCHEMA) ?

    Id like a defined result set rather than one returned from a sp, because I want to left join with the results
    Chappy, Jan 6, 2003
    #2

  2. satya Moderator

    I use the following query to subsidise one report about indexes on the databases:
    ----
    select convert(char(30),t.name), indid,i.status, convert(char(30),i.name)
    from sysobjects t
    left outer join sysindexes i
    on t.id = i.id
    where t.type = 'U'
    and t.name like 'T[_]%'
    and (indid<> 0
    or indid is null)
    order by 1,2
    ---

    HTH

    Satya SKJ
    satya, Jan 6, 2003
    #2

  3. Chappy New Member

    Thanks a lot satya!
    Chappy, Jan 8, 2003
    #2

  4. Chappy New Member

    Do you know of any way to decode information from the sysindexes.status field? Books online says it is reserved and internal, so I assume there isnt (at least not an approved way <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Or maybe Im approaching this from the wrong angle, My goal is to list all tables which do not have a clustered index on them. Thanks.
    Chappy, Jan 8, 2003
    #2

  5. satya Moderator

    The indid columns value represents :

    ID of index:
    1 = Clustered index
    >1 = Nonclustered
    255 = Entry for tables that have text or image data

    HTH


    Satya SKJ
    satya, Jan 8, 2003
    #2

  6. royv New Member

    What about hypothetical indexes SQL Server creates? I'm not sure, but I believe they also have a value of 255


    "How do you expect to beat me when I am forever?"
    royv, Jan 8, 2003
    #2

Share This Page