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
Wednesday, February 20, 2008
How to get indexes at Sql Server 2005 using query
Labels:
SQL Server
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment