Wednesday, February 20, 2008

How to get indexes at Sql Server 2005 using query

I have query that make you easier to find index. here it is. I hope i would be able to help you to make easier.

SELECT
    o.name,
    i.name,
    RTRIM(LTRIM(ISNULL(MIN (CASE ik.keyno WHEN 1 THEN c.name     END),'') +
    (CASE WHEN ISNULL(MIN (CASE ik.keyno WHEN 2 THEN c.name     END),'T') = 'T' THEN '' ELSE ',' END) +
    ISNULL(MIN (CASE ik.keyno WHEN 2 THEN c.name END),'') +     (CASE WHEN ISNULL(MIN (CASE ik.keyno WHEN 3 THEN c.name     END),'T') = 'T' THEN '' ELSE ',' END) +
    ISNULL(MIN (CASE ik.keyno WHEN 3 THEN c.name END),'') +     (CASE WHEN ISNULL(MIN (CASE ik.keyno WHEN 4 THEN c.name     END),'T') = 'T' THEN '' ELSE ',' END) +
    ISNULL(MIN (CASE ik.keyno WHEN 4 THEN c.name END),'') +     (CASE WHEN ISNULL(MIN (CASE ik.keyno WHEN 5 THEN c.name     END),'T') = 'T' THEN '' ELSE ',' END) +
    ISNULL(MIN (CASE ik.keyno WHEN 5 THEN c.name END),'') +     (CASE WHEN ISNULL(MIN (CASE ik.keyno WHEN 6 THEN c.name     END),'T') = 'T' THEN '' ELSE ',' END) +
    ISNULL(MIN (CASE ik.keyno WHEN 6 THEN c.name END),'') +     (CASE WHEN ISNULL(MIN (CASE ik.keyno WHEN 7 THEN c.name     END),'T') = 'T' THEN '' ELSE ',' END) +
    ISNULL(MIN (CASE ik.keyno WHEN 7 THEN c.name END),'') +     (CASE WHEN ISNULL(MIN (CASE ik.keyno WHEN 8 THEN c.name     END),'T') = 'T' THEN '' ELSE ',' END) +
    ISNULL(MIN (CASE ik.keyno WHEN 8 THEN c.name END),'') +     (CASE WHEN ISNULL(MIN (CASE ik.keyno WHEN 9 THEN c.name     END),'T') = 'T' THEN '' ELSE ',' END) +
    ISNULL(MIN (CASE ik.keyno WHEN 9 THEN c.name END),'') +
    ISNULL(MIN (CASE ik.keyno WHEN 10 THEN c.name END),'')))
FROM sysobjects o
    JOIN sysindexes i ON i.id = o.id
    JOIN sysindexkeys ik ON ik.id = i.id
        AND ik.indid = i.indid
    JOIN syscolumns c ON c.id = ik.id
        AND c.colid = ik.colid
    JOIN INFORMATION_SCHEMA.TABLES IFT ON IFT.table_name = o.name
        AND IFT.table_type = 'BASE TABLE'
WHERE i.indid BETWEEN 1 AND 254
    AND indexproperty(o.id, i.name, 'IsStatistics') = 0
    AND indexproperty(o.id, i.name, 'IsHypothetical') = 0

--And o.name = 'Addresses' -- specify your table
GROUP BY o.name, i.name
ORDER BY o.name, i.name

No comments: